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
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.
- New Table - Create a
TermTimeAddresstable with a 1:1 relation to
- New Fields - Add extra fields to the
Studenttable 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?