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
numbers!

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
contact numbers.

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...

Thanks

yes, it's reasonable, but i think you can simplify it a bit

from your description, a family will have a phone number, not several phone numbers, so put phone_id into FAMILIES and drop FAMILY_PHONE

far as i know a person belongs to only one family, so put family_id into MEMBERS and drop FAMILY_MEMBER

and assigning a phone number to an occupation seems wrong

Thanks r937,

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!

All advice welcome!