1

Possible Duplicate:
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server

I have two tables, Users and People, both of which share a common attribute, email address, of which they should be allowed to have many email addresses. A User has many People, a Person belongs to a User.

I can see three options myself:

  1. One link table with redundant columns:

    • Users [id,email_id] and People [id,email_id]
    • EmailAddress [user_id,person_id,email_id]
    • Emails [id,address,type]
  2. Two link tables without redundancies:

    • Users [id,email_id] and People [id,email_id]
    • PersonEmail [person_id,email_id]
    • UserEmail [user_id,email_id]
    • Emails [id,address,type]
  3. No link tables with redundant columns:

    • Users [id] and People [id]
    • Emails [id,address,type,user_id,person_id]

Does anyone have any idea what would be the best option, or if there is any other ways?

UPDATE: I figured that it may be a good idea to use inheritance. In the system, the User is conceptually different as they have permission to login and use the system and they retain information on people, much like having their own address book. Yes, the users and people will share many attributes, over name, email, phone, address, etc.

marked as duplicate by gbn, JNK Jun 4 '12 at 15:36

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Too little information to give a good directive here. For instance, a User is related to multiple People. But can each People record have multiple Email addresses? Likewise, can a User have an email address (or multiple) that aren't provided by the People records that are related? – Thomas Stringer Apr 2 '12 at 19:29
  • I've just updated the question: they will share a lot of attributes and on many, they should be allowed to have many occurrences of those attributes, (more than one phone number for example) – user7442 Apr 2 '12 at 19:33
  • I'm certain to be using inheritance to help resolve this problem. Any one who could then suggest the best option with it(?) – user7442 Apr 2 '12 at 19:37
  • I decided to have a Person with specialised sub-types of User and Contact with the person_id as a foreign key in the Email entity. – user7442 Apr 2 '12 at 20:05
  • Your last comment indicates a soklution like this dba.stackexchange.com/q/13470/630 – gbn Apr 3 '12 at 7:07