What is the best way to create tables with users who can have multiple emails?

In my opinion, the best way to "organize" is to use 1:m , something like:

SQLs:

CREATETABLEIFNOTEXISTS'clientes'('idclientes'INTUNSIGNEDNOTNULLAUTO_INCREMENT,'nome'VARCHAR(45)NULL,PRIMARYKEY('idclientes'))ENGINE=InnoDB;CREATETABLEIFNOTEXISTS'emails'('idemails'INTUNSIGNEDNOTNULLAUTO_INCREMENT,'email'VARCHAR(80)NULL,'clientes_idclientes'INTUNSIGNEDNOTNULL,PRIMARYKEY('idemails'),INDEX'fk_emails_clientes_idx'('clientes_idclientes'ASC),CONSTRAINT'fk_emails_clientes'FOREIGNKEY('clientes_idclientes')REFERENCES'clientes'('idclientes')ONDELETENOACTIONONUPDATENOACTION)ENGINE=InnoDB;

Dataexample:

INSERTINTO'clientes'('nome')VALUES('João'),('Maria'),('Raul');INSERTINTO'emails'('idemails','email','clientes_idclientes')VALUES('[email protected]',1),('[email protected]',1),('[email protected]',1),('[email protected]',2),('[email protected]',2);

Ifyouwanttodisplayallclients,eventhosewhoarenotusingemailuseLEFTJOIN:

SELECT'clientes'.nomeASCLIENTE_NOME,'emails'.emailASCLIENTE_EMAILFROM'clientes'LEFTJOIN'emails'ON'emails'.clientes_idclientes='clientes'.idclientesWHERE1;

Result:

CLIENTE_NOME|CLIENTE_EMAIL==================================João|[email protected]ão|[email protected]ão|[email protected]|[email protected]|[email protected]|NULL

IfyouwanttodisplayonlyclientsthathaveemailuseINNERJOIN:

SELECT'clientes'.nomeASCLIENTE_NOME,'emails'.emailASCLIENTE_EMAILFROM'clientes'INNERJOIN'emails'ON'emails'.clientes_idclientes='clientes'.idclientesWHERE1;

Result:

CLIENTE_NOME|CLIENTE_EMAIL==================================João|[email protected]ão|[email protected]ão|[email protected]|[email protected]|[email protected]

  NotethatIusedLEFTJOINbecauseonlytheclientstablehasno"constraints", a line in the email table must have a client in the clientes table, in case you want to create "loose" FULL OUTER JOIN

     

To understand more about this I would like you to look at this great answer on the subject:

     

What's the difference between INNER JOIN and OUTER JOIN?

The best way is for you to create your customer table with your fields, then create the e-mail table with its fields and a secondary customer key.

You just need to edit your email _ client table instead of setting the two fields as primary keys just set email _ id and set the other as the secondary key and add your other email field.