0

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?

3

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.

  • 1
    Well thankfully I'm not, so I can still do changes at this point. I'm assuming if I'm marking the address type I just use a unique constraint to validate it? – Jake Jul 26 '16 at 17:35
  • Yes, depends on business rules. You could enforce only having one of each contact type for each person, or that email addresses can only belong to one person - but before you do that, be sure it makes sense. If there are cases where you might want multiple email addresses or home addresses (say you wanted to track address history and not just current address) you might not want to do that. – Fred Shope Jul 26 '16 at 18:27
  • The natural key to ContactInformation is PersonID and ContactType together. ContactID is a surrogate ID that, while useful, is not necessary. – Michael Green Jul 27 '16 at 0:53
-1

Your already have it setup just create a new record in the "ContactInformation" table with the same PersonID and then the person has two addresses

  • PersonID is the PK in ContactInformation. Your suggestion will violate the PK constraint. – MguerraTorres Jul 26 '16 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.