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.

Community
  • 1
  • 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 02 '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) –  Apr 02 '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(?) –  Apr 02 '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. –  Apr 02 '12 at 20:05
  • Your last comment indicates a soklution like this http://dba.stackexchange.com/q/13470/630 – gbn Apr 03 '12 at 07:07

0 Answers0