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

I do not think you're right about the N:N relationship that you described. As far as I know, the relationship between:

• clients and phones: one-to-many
• providers and phones: one-to-many
• phones and customers: many-to-one
• phones and providers: many-to-one

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

Ofcourse,thereisnotaforeignkeyexplicitlyandformallydefinedinthephonestable.However,theconceptstillremains,sincethetelefones.quemfieldshouldpointtoaprimarykeyofthesupplierstableorclientstable.

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

Withtheaboveschemeyouhavetelefones.quemreferencingaclientes.idorforncedores.id.ThisevenfacilitatesaSQLqueryforonlycustomerorvendorphonesthroughthetelefones.is_clientefield.

Insummaryyouwouldhave:

Pros

• Flexibility(easytochangeandmanage)
• Justatablewithphones,lessJOINS,easiertoquery(inthiscase);
• Cleanandmeaningfullayoutandstructure;

Cons:

• Thereisnoexplicitrestrictiononrelationalintegrity;
• ForeignKeyoptions/actionssuchasONDELETECASCADEisnotavailable;
• Onceyoudeleteacustomerorvendorrecord,youwillhavetomanuallydeleteyourreferencesinthetelefonestableifthisisimportant;

  

... practicality beats purity .

There's no reason not to have two tables, is your customer your supplier, or vice versa? I think not, and even if that happens I do not think it would happen more than 10 times ...

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

Solutions like:

TelefoneRelacionado
ID_TELEFONE   
ID_CLIENTEouFORNECEDOR

or

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

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

Another point is that you will not replicate data using two tables, you will not have a single phone number written in the two tables, because of the fact that you did not have many customers that are suppliers and vice versa.

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

If the system is so large as to cause such a concern then it is worth thinking about how much it is running and full of data, how SQLs will be done.

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

PS: I do not recommend using automatic exclusion rules for foreign keys, as this can affect data loss, my suggestion is, do it by code, it's safer, bank, passing the responsibility to the system.