I'm designing a database to hold our students and their parents/guardians. Heres what I have so far:
Conditions
- I need to identify student siblings (ie; brothers/sisters that are part of the same family)
- A family can have an unlimited number of guardians
- Brothers/Sisters always have the same set of guardians
- Guardians may share the same address
- Guardians may have no address, could just be an email or phone number
My joining table [families] seems a little redundant to me. Is this the best way to do it? Even though its sparse now I can see us having to add family level information in the future. For example invoices are sent out on the family level, not per student or guardian.
Should I store email/phone numbers in a seperate table?
Best Answer
How about creating a separate Table called Person ? You will need separate tables for storing Guardian or Student related information and they both can refer to person Id.
Instead of family you can have relation table that joins two different Person with Extra column of RelationshipType (and of course another lookup table.)
Address table can connect to person table as well and same as Address you can have another table for contact details as well with contact type lookup table that stores values like phone number, email, mobile, work email, work phone etc.
This will also help you in scenarios where an ex student becomes a guardian.
Hope that helps.
Maulin Thaker