How to relate tables in Mysql to more than one entity?

Question

How can I relate a table(entity) to more than one entity in the Mysql database? following the following example...

I have the tables: fornecedores, clientes and telefones.

• The fornecedores, may have more than one phone.
• The clientes, may also have more than one phone.

To solve this problem, I created a relationship N:N, where a fornecedor may have several telefones, but one phone does not belong to several fornecedores. That’s right, same for clientes, where a customer may have several telefones, but one phone does not belong to several clientes.

With this relationship N:N, an intermediate table was created between fornecedores and telefones, and also between clientes and telefones.

How could I solve this problem without having a phone table to relate to fornecedores, and another phone table to relate to clientes? without it being N:N?

Answer 1

I don’t think you’re right about the relationship N:N according to what you described.
As far as I know, the relationship between:

• customers and phones: one-to-Many
• suppliers and phones: one-to-Many
• phones and customers: Many-to-one
• phones and suppliers: Many-to-one

Also, from the pragmatic point, I think creating a table such as telefones_clientes and telefones_forncedores is not a good idea, since there is a redundancy to the unnecessary principle. That said, I think a more sensible and practical approach would be something like:

Of course, there is an explicit and formally defined foreign key in the phones table. However the concept still remains, since the field telefones.quem should point to a primary table key suppliers or customers.

FORNECEDORES |           +-------------+         | CLIENTES    |
------------ +           | TELEFONES   |         +-------------+
 id          | ____      +-------------+     ___ | id          |
------------ +      \    | id          |    /    +-------------+
 nome        |       \___+-------------+___/     | nome        |
------------ +           | quem        |         +-------------+
                         +-------------+
                         | is_cliente  | -- é cliente (1) ou fornecedor (2)? --
                         +-------------+
                         | telefone    |
                         +-------------+

With the above scheme you have telefones.quem referencing a clientes.id or forncedores.id. This even facilitates an SQL query by customers' or suppliers' phones only through the field telefones.is_cliente.

In short you would have:

Pros

• Flexibility (easy to change and manage).
• Only one table with phones, less JOINS, easier to consult (in this case);
• Schematic and structure clean and that makes sense;

Cons:

• There is no explicit restriction on relational integrity;
• Foreign Key options/actions such as ON DELETE CASCADE is not available;
• Once you delete a customer or vendor record, you will need to manually delete your references in the table telefones, if this is important;

---

A famous Python "mantra" (Zen of Python) comes in handy here:

... practicality Beats purity.

Answer 2

There is no reason not to have two tables, your customer will also be your supplier or vice versa? I believe not, and even if it happens I don’t think it would happen more than 10 times...

Starting from this principle then the ideal is to have two tables, even to organize access plans in the database.

Solutions such as:

TelefoneRelacionado
ID_TELEFONE   
ID_CLIENTEouFORNECEDOR

or else

TelefoneRelacionado  
ID_TELEFONE  
ID_CLIENTE (pode null) 
ID_FORNECEDOR (pode null)

Besides complicating the life of maintenance can make the database crazy in the access plans, in case the example one will not have foreign key connecting the tables.

Another point is that you will not replicate data using two tables, you will not have a single phone number recorded in both tables, for the fact quoted at the beginning of not having many customers who are suppliers and vice versa.

That would be my answer to your question. The two solutions above are ways to do this, they are not elegant and I dare say they are bad. (my point of view only)

If the system is so big to cause a concern like this then it is worth thinking about how much it is running and full of data, how the Sqls will be made.

Answer 3

You are doing a wrong thing, the correct thing is you relate the person/user (or your entity) to the phones, and your entity you relate to his profile, even if he already has an entity, you create a control (phone or customer). This way you can dynamically include supplier, distributor, dealer, customer, seller and so on...

1. entity->telephones 1:N (one-to-Many);
2. entity->profile 1:N (one-to-Many);
3. entity->profile N:N (Many-to-Many);

PS: I do not recommend using automatic rules of deletion by foreign keys, because this can affect in data loss, my suggestion is, do it by code, is safer and takes the responsibility of the bank, passing the responsibility to the system.