Customer must have at least 3 e-mails

Question

I'd like to create a database model where every customer must have at least 3 e-mails.

The first idea is to model it as follows (I will use MySQL syntax):

create table customer (
  id int not null auto_increment primary key,
  ...
  text e-mail1 not null,
  text e-mail2 not null,
  text e-mail3 not null);

create table customer_emails(
    id int not null auto_increment primary key, 
    text e-mail
    ...
    foreign key (customer_id) references customer(id));

You can notice that in this model there is a possibility for data redundancy.

Let's assume an example we want to insert a customer having 5 e-mails (e1, e2, e3, e4, e5) into the database. Three e-mails (e1, e2, e3) will be certainly inserted into table customer. Now, we have 2 possibilities.

1. insert e4, e5 to customer_emails
2. insert e1, e2, e3, e4, e5 to customer_emails

What is better approach and why? 1 (no redundancy) or 2 (with redundancy)?

Answer 1

The second option is better. However in your case, all customer emails should be stored in customer_emails regardless of if they have 3 or 25.

If you have multiple emails that a customer can have, you should not store any of them in the customer table. Instead store all of them in the customer_emails table. Get rid of the e-mail1, e-mail2, e-mail3. It would be confusing to have to get three emails from the customer table and the rest from another table.

Image how you would get all of the emails for a particular customer in scenario 1:

SELECT c.id, c.email1, c.email2, c.email3, ce.email
FROM customer c
INNER JOIN customer_emails ce on ce.customer_id = c.id
WHERE id = 67

The above would return:

c.id  c.email1     c.email2     c.email3    ce.email  
67    [email protected]   [email protected]   [email protected]  [email protected]
67    [email protected]   [email protected]   [email protected]  [email protected]
67    [email protected]   [email protected]   [email protected]  [email protected]
67    [email protected]   [email protected]   [email protected]  [email protected]
67    [email protected]   [email protected]   [email protected]  [email protected]
67    [email protected]   [email protected]   [email protected]  [email protected]

Now think about this, scenario 2:

SELECT c.id, ce.email
FROM customer c
INNER JOIN customer_emails ce on ce.customer_id = c.id
WHERE id = 67

would return:

c.id  ce.email
67    [email protected]
67    [email protected]
67    [email protected]
67    [email protected]
67    [email protected]
67    [email protected]
67    [email protected]
67    [email protected]
67    [email protected]

The second option is much easier/cleaner to deal with since all the email addresses are in one field.