I am new to databases, and am "playing" with trying to convert a flat database to a relational database but am having a lot of difficulty in
understanding how to set up some of the relationships, especially the phone
This is for an organization that registers families and keeps contact information (phone numbers) on each of the family members.
The family will have a home phone (required).
Each family member can have zero or more phone numbers (cell, pager, fax, etc.).
The family members may also be employed and wish to include zero or more work
In normalizing my tables I have come up with the following:
FAMILIES : family_id, family_address, family_joindate, family_duespaid
MEMBERS : member_id, member_firstname, member_lastname, member_type
(members within a family may not have the same last name,
the member_type is used to identify adults from children)
OCCUPATION: occupation_id, company_name, occupation
PHONES : phone_id, phone_number
with the following cross-reference tables:
FAMLIY_PHONE : family_id, phone_id
FAMILY_MEMBER : family_id, member_id
MEMBER_PHONE : member_id, phone_id
MEMBER_OCCUPATION : member_id, occupation_id
OCCUPATION_PHONE : occupation_id, phone_id
First, is this a reasonable setup?
Second, if members (within a family) initially list a common phone number,
what is the best way to deal with the case where one member updates their
number. I am guessing that I should ask if the update is to be for all members
sharing that number or to add a new phone to the list...
I am trying to understand the relationship between optimum normalization and practical implementation, so your suggestion on dropping FAMILY_PHONE seems reasonable.
As far as the phone(s) for the occupation, these would be work phone, work fax, etc. The current flat database has a total of 26 phone fields for each family (cell, fax, work, work pager, work fax for two adult family members, 1 cell for each child (for up to 15 children) plus the home phone number!) even though most families only list 3 to 5 numbers!