Foreign tables in SQL Server updated with Primary Key

Question

The title might be a little misleading because I didn't really know what to call it. Here is my dbml and I am using the repository pattern to communicate between my application layer and my SQL layer. As you can see, I have four tables. Three of them have a foreign key to ContactId. I used this method because I need to store an "array", so I made several tables. So now I am wondering how to make it so that I can "add" an email address, phone number, or address to a contact and manipulate it through the contact.

Answer 1

The "obvious" answer is to have a Contact class which has addEmail, addPhone, and addAddress methods, plus the matching stored procedures in the database. Is there more to your question than this?

Also, while this is not directly part of your question, you may want to think about normalizing your design. The typical way to handle these types of things is with one-to-many tables. For example, your email address table would look like this:

EmailId (int - PK)
ContactId  (int - FK to Contact)
EmailAddress (varchar)

OR maybe:

ContactId  (int - FK)
EmailNumber (tinyint? sequential 1, 2, 3, ...)
EmailAddress (varchar)

with the PK being ContactId and EmailNumber

Either way, each row stores a single email address. This should make it easier to add or remove individual emails, and removes the limit of three emails in your current design. It also makes it easier to add additional info about each email address. For example, is it a home or work address. Or, is the email address valid.

Answer 2

If you wanted a fully normalized design then you should have something like

Create Table dbo.Contacts
(
    Id int not null Primary Key Clustered
    , FirstName nvarchar(25) null 
    ...
)

Create Table dbo.ContactAddresses
(
    Id int not null Primary Key Clustered
    , ContactId int not null
    , Street nvarchar(max) null
    , City nvarchar(40) null
    ...
    , Constraint FK_ContactAddresses_Contacts
        Foreign Key ( ContactId )
        References dbo.Contacts( Id )
)

Create Table dbo.ContactEmail
(
    Id int not null Primary Key Clustered
    , ContactId int not null
    , Type nvarchar(10) not null
    , Address nvarchar(255) not null
    ...
    , Constraint CK_ContactEmail_Type Check Type In('Primary','Secondary',...)
    , Constraint UK_ContactEmail_Address Unique ( ContactId, Type, Address )
    , Constraint FK_ContactEmail_Contacts
        Foreign Key ( ContactId )
        References dbo.Contacts( Id )
)

Create Table dbo.ContactPhone
(
    Id int not null Primary Key Clustered
    , ContactId int not null
    , Type nvarchar(10) not null
    , Number varchar(20) not null
    ...
    , Constraint UK_ContactPhone Unique ( ContactId, Type, Number )
    , Constraint CK_ContactPhone_Type Check Type In('Office','Fax','Home',...'Mobile')
    , Constraint FK_ContactPhone_Contacts
        Foreign Key ( ContactId )
        References dbo.Contacts( Id )
)

If you want a more controlled list of the email or phone types, you could add a parent table and a foreign key to store them. What is missing from this design is protection against address dupes. Is a user required to enter the full address? This design allows for unlimited emails, phone numbers and addresses. However, that may be overkill for what you need.

If you are designing a full blown contact manager to replace something like Outlook, then the above design would be the way to go. However, most systems which capture some contact data are not meant to be full contact managers. If that is the case, then you should place some reasonable constaints on the amount of data you will capture. For example, are you really going to allow users to enter eight phone numbers for a single contact? It is probably more realistic that 99% of the contacts will only have two or three phone numbers. Include an nvarchar(max) Note column in the contact for people that want to capture hundreds phone numbers and email addresses. If that is the case, then you can denormalize your design a bit:

Create Table dbo.Contacts
(
    Id int not null Primary Key Clustered
    , FirstName nvarchar(25) null 
    ...
    , PrimaryEmailAddress nvarchar(255) null
    , SecondaryEmailAddress nvarchar(255) null
    , OfficePhone nvarchar(25) null
    , MobilePhone nvarchar(25) null
    , FaxPhone nvarchar(25) null
    , Note nvarchar(max) null
    ...
    , Constraint UK_Contacts Unique ( FirstName.... )
)

The only question you have at this point regards addresses. Before I get to that, if management says "Hey we want to store five more phone numbers" you should tell them that the system is not a replacement for Outlook. If a user wants to store more phone numbers than you have provided, then put it in the Note column. With addresses, you would need to know how many addresses you would need to store. If it is more than two (or even one), my suggestion would be to still put it in a separate table.

Flexibility is related 1:1 to complexity. Managing an unlimited number of email addresses, phone numbers and addresses is more flexible, but adds more work in the UI. Most contact capturing designs do not really need to capture dozens of email addresses for a given contact and thus a fully normalized design is simply adding more work than is necessary.