2

I'm designing a database to hold our students and their parents/guardians. Heres what I have so far:

enter image description here

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?

Improve this question
3
  • Just as with mailing addresses, one guardian may have multiple email addresses and/or phone numbers. Home, work, mobile (for phone), home & work for email, maybe even others if they offer them up. Putting them in their own tables may be beneficial so you don't run out of fields if a guardian gives you too many to fit.
    – alroc
    Oct 5, 2016 at 19:00
  • Would you use separate tables for email and phone (and address?), or combine them somehow in a guardian_contacts type table?
    – Alex
    Oct 6, 2016 at 1:05
  • 1
    There's no relationship/correlation between email and phone (the only connection between them would be the guardian(s) they're tied to, but you can't say that 800-55JIMBO maps directly to [email protected]), and (for example) one phone number may be used by multiple guardians.
    – alroc
    Oct 6, 2016 at 1:45

1 Answer 1

Reset to default
4
+50

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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