CODEWITHSUNDEEP
sqldatabasejoindatabase-designreferential-integrity
ratherBeKiting
ratherBeKiting

Reputation: 275

Best practise for polymorphic associations in MYSQL

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)

Upvotes: 4

Views: 3544

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18950

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:

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.

Upvotes: 4

Misa Lazovic
Misa Lazovic

Reputation: 2823

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.

Upvotes: 1

Related Questions