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

Question

What is the best way to create the email table, as the client may have several emails,

I created two situations one email and another email?

what is the best way taking into account processing, query agility,

Could you help me?

Answer 1

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

Sqls:

CREATE TABLE IF NOT EXISTS `clientes` (
  `idclientes` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  PRIMARY KEY (`idclientes`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `emails` (
  `idemails` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(80) NULL,
  `clientes_idclientes` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`idemails`),
  INDEX `fk_emails_clientes_idx` (`clientes_idclientes` ASC),
  CONSTRAINT `fk_emails_clientes`
    FOREIGN KEY (`clientes_idclientes`)
    REFERENCES `clientes` (`idclientes`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Data example:

INSERT INTO `clientes` (`nome`) VALUES
('João'),
('Maria'),
('Raul');

INSERT INTO `emails` (`idemails`, `email`, `clientes_idclientes`) VALUES
('[email protected]', 1),
('[email protected]', 1),
('[email protected]', 1),
('[email protected]', 2),
('[email protected]', 2);

If you want to display all customers, even those without emails use LEFT JOIN:

SELECT
  `clientes`.nome AS CLIENTE_NOME,
  `emails`.email AS CLIENTE_EMAIL
FROM
  `clientes` LEFT JOIN `emails` ON `emails`.clientes_idclientes = `clientes`.idclientes
WHERE 1;

Upshot:

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

If you only want to display customers who have emails use INNER JOIN:

SELECT
  `clientes`.nome AS CLIENTE_NOME,
  `emails`.email AS CLIENTE_EMAIL
FROM
  `clientes` INNER JOIN `emails` ON `emails`.clientes_idclientes = `clientes`.idclientes
WHERE 1;

Upshot:

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

Note that I used LEFT JOIN as only the table customers has no "restrictions", a row in the table email must have a client in the table clientes, if you want to create "loose" emails you will have to use FULL OUTER JOIN

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

What is the difference between INNER JOIN and OUTER JOIN?

Answer 2

The best way is for you to create your customer table with your fields, then create the email table with your fields and a secondary client key.

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