4

I checked this question here but unfortunately the link to the diagram in not working so I'm stuck.

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

I am thinking to 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 better one.

First diagram helps me if one user has the same email for both work and personal (because I don't have to store it twice). Second option is good as well but I would have to store emails twice or more even if one user uses the same email for work, business, personal, etc.

I am planning to use the same idea for storing addresses, which occupy more space than emails and I am thinking that the diagram 1 is more suitable for this.

What do you think?

Diagram 1
-explanation of user_has_email: I chose to make the email_type PK because there may be the case when a user has the same email for work or personal. If I don't PK the email_type I would only be able to have one email_type per user. Did I complicated it too much?

Diagram 1


Diagram 2 Diagram 2

Improve this question
2
  • 1
    Both of your diagrams implement a many-to-many relationship. Do you need to support the possibility that a single email address is shared by more than one person? Commented Jun 14, 2013 at 17:24
  • @Dan Pichelman - Yes that would be a possibility but I don't think that in the emails case. Since I plan to use the same model for storing user's addresses I am sure that there will be users sharing the same address. Or, at least there will be users that use an address for both personal and business. Commented Jun 14, 2013 at 19:35

3 Answers 3

Reset to default
4

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Okay. I got your point. That would eliminate a table and keep thing simple. However, if I want to do the same for addresses (which hold much more information), in case I would have users sharing an address I would have to store the same thing more than once. Same thing applies for emails.
This does not allow the same e-mail to have multiple types (e.g. to be both "work" and "personal" e-mail).
1

I would prefer Diagram 1 for the following reasons.

  1. You can make the email field UNIQUE so that you can store it only once regardless of the type.
  2. It does not seem right to make the email and the email type tightly coupled, if you face a situation where you have to establish a one-to-one relationship between the user and the email for some other feature.
  3. Any kind of validation for the user-email relationship should be handled in the business logic (even if you have constraints in the database).
Improve this answer

Comments

1

The following structure should fit the bill:

enter image description here

There is a 1:N relationship between users and e-mails, and each user's e-mail can have zero or more types, from the set of shared types.

If the e-mail types don't need to be shared among users, the model can be further simplified:

enter image description here

BTW, the case for using M:N for addresses is not clear either, due to the inherent "fuzziness" of addresses - see this post for some musings on the subject.

Improve this answer

2 Comments

Thanks for your answer. For the first structure, in the email table, since email_id is PK I won't be able to have the same email for different users unless I would make user_id PK as well! I noticed your structure is very similar with my first one. Is there any difference between the two?
Disregard my first comment. For emails, the first structure works, but for addresses it won't. However, your answer made me realize that I was going way to far from what I need to do. So, I will only share an address between the same user (work / personal/ billing etc) and in case two or more users share the same address I will just have to create another one. This way it will be easier when someone needs to change their stored address.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.