MYSQL - the best design for storing multiple email addresses / addresses for one user

Question

I checked this question here , but unfortunately the link to the diagram does not work, so I am stuck.

I am trying to have several emails for one user (work, business, personal, etc.), and I'm not sure how best to approach this situation.

I think I have 4 tables: user, email, email_type and user_has_email (user N: M email). I made two diagrams, but I don’t know which one would be the best.

The first diagram helps me if one user has the same email address both for work and personal (because I do not need to store it twice). The second option is good, but I will need to store emails twice or more, even if one user uses the same email address for work, business, personal, etc.

I plan to use the same idea to store addresses that take up more space than emails, and I think that diagram 1 is suitable for this.

What do you think?

Chart 1
-explanation user_has_email: I decided to make mail_type PK, because there may be times when the user has the same email address for work or personal. If I am not PK email_type, I will only have one email_type per user. Am I complicating too much?

Chart 2

Answer 1

Instead, I would use

user (user_id, first_name, last_name) user_emails (user_id, email_type_id, email) email_types (email_type_id, email_type) 

Answer 2

I would prefer Diagram 1 for the following reasons.

• You can make the UNIQUE email field so that you can save it only once, regardless of type.
• It does not seem that the email message and the type of email are closely related if you are faced with a situation where you need to establish an individual connection between the user and the email for some other function.
• Any kind of user-email verification should be handled in business logic (even if you have database restrictions).

Answer 3

The following structure should correspond to the score:

There is a 1: N relationship between users and email, and each user email address can have zero or more types from a set of common types.

If email types should not be shared among users, the model can be further simplified:

By the way, the case of using M: N for addresses is also not clear due to the inherent “blurring” of addresses - see this post for some thoughts on the subject.