Best practise for polymorphic associations in MYSQL

Question

I have a database with tables: clients (client_id) and companies (company_id).

Both clients and companies can have addresses (address_id), stored in the addresses table.

Is it better practice to:

1. Have 2 extra tables linking to companies and addresses:

   clients(client_id)
   client_addresses(client_id, address_id)
   companies(company_id)
   company_addresses(company_id, address_id)
   addresses (address_id)
   

2. Have a single table entities which has a primary key entity_id used to link all 3 tables:

   entities(entity_id)
   clients(client_id, entity_id)
   companies(company_id, entity_id)
   addresses (address_id, entity_id)
   

Answer 1

There is another alternative. Clients and Companies can be considered subclasses (or, if you prefer, subtypes) of a more generic entity, which might be called contacts. A client is a contact and a company is a contact. I'll skip over whether or not a contact can be both a client or a company. If you choose to model things this way, you are faced with a problem due to the absence of inheritance (nevermind polymorphism) in the relational model. There are several ways around this that various people have implemented with somewhat good results. You can see these techniques outlined in three tags here in SO:

single-table-inheritance class-table-inheritance shared-primary-key

The last technique is used in conjunction with the second one.

In addition to chasing down the info and the questions in each tag, you can do further research on the web.

Answer 2

I'd say the only difference is in case you need some data specific to client_addresses or company_addresses to be stored - then the first approach will be better. Otherwise, you can use the second approach.

For example: if you wanna store number of employees a company has on certain address, then the first approach should be your choice. And if the relation of client or company to address is a simple key pair, you could use the second approach to work with fewer tables.

One more thing you have to figure out is if "entity" gives you any value. E.g. client_addresses stores clients on addresses (at least only keys) and what does entities store? If you want to get all addresses for a client with some ID, you'd have to join client_addresses and addresses; while with entities, you'd need to join 3 tables for the same thing.

If you ask me, I'd go with the first approach.