Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Setting up a membership database

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •