Storing multiple contact addresses for a person

database-designnormalization

Simplified diagram for reference.
Simplified diagram for reference

My database stores information about people in the following way. There is a Person table containing common personal information fields; name, date of birth, gender, etc.

The contact information for each person is kept in the ContactInformation table with a 1:1 relationship.

Finally, there are then individual tables for each specific type of person in the organisation we store data on with a 1:1 relation to Person

I have a bit of a problem with the Student table. Basically my client now wants to store both a home address and term time address for a student. But I'm not sure how to approach this.

I see three possible options/solutions:

  • Junction Table – Create a junction table (e.g. ContactAddressMapping) between Person and ContactInformation.
  • New Table – Create a TermTimeAddress table with a 1:1 relation to Student
  • New Fields – Add extra fields to the Student table to hold the term time address.

Now here are the main "problems" I see with each option:

A junction table would allow multiple addresses to be stored for a person, but we'd only ever need to do this for students (for now). There would also be no way to determine which address is the home/term time address without using some kind of boolean field which seems like pointless information in that table since it's only unique to students.

Creating a new table separates the term time address from the main address but these feels like duplication since I'm basically creating a specific type of address with the same fields, minus things like phone numbers and email addresses.

Adding the fields to the Student seems like I'm cluttering the table with information that should be elsewhere. The table should only be for information about that student such as their school, the area of study and the start and end date of their placement.

Which of these would be the most appropriate option, or is there another solution that I haven't seen yet?

Best Answer

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own PK, an FK for PersonID back to the Person table, and store as many Contacts as necessary for each Person.

CREATE TABLE ContactInformation (
    ContactID int IDENTITY(1,1) PRIMARY KEY,
    PersonID int FOREIGN KEY REFERENCES Person(id),
    ContactInfo nvarchar(250),
    ContactType char(2)
)

Populate the ContactInformation table with the PersonID, then an email address/phone number/mailing address/twitter handle/etc and mark it with a ContactType. This could be 'MA' for mailing address, 'EM' for email, "TW' for Twitter, 'TT' for Term Time as in your question.

Triggers could validate any inserts to make sure contacts don't have a TT (term time) address unless they're a student.

Hope that helps you think outside the box. It may be too hard to make these changes at this point, if you're already deep in the application.

Edit: I didn't feel I properly addressed your direct question. If I were you and making deeper changes isn't possible or feasible at this point, I would add TermTime address to the Students table.

It belongs there because 1) it only applies to Students, so having it in the Person or Contact tables (as they exist now) is less than ideal, and 2) Each student should have one so it's already part of their student record and 3) this can be done with the least disruption to the existing schema.

Related Question