Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Common table linked to two others

    Hi all,

    I have a company table. A company can have any number of phone numbers. I also have a contact table. A contact can also have any number of phone numbers (cell, home, pager ...). Contacts are related to companies in that a company can have one or more contacts.

    My design right now is as follows:

    company table:
    company_id (pk)
    company_name
    company_address
    ...

    company contact table
    company_contact_id (pk)
    company_id (fk)
    company_contact_name
    ...

    At this point, I thought of using one phone table to store both company and contact phone numbers, but I can't figure out how to link them ... A company can have zero, one or many phone numbers. A contact can also have zero, one or many phone numbers. A company doesn't necessarily have to have a contact, but a contact has to be related to a company.

    Then only way I can think of doing it is by having two phone tables:

    company phone table
    phone_id (pk)
    company_id (fk)
    phone
    phone_ext
    phone_type
    ...

    contact phone table
    phone_id (pk)
    company_contact_id (fk)
    phone
    phone_ext
    phone_type
    ...

    Is this the right way to go about it?

    Thanks
    Julio

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Yes, that's perfectly reasonable. I imagine you're a bit concerned about the duplication in the 2 table definitions; if there was a lot more to a contact phone than the 3 or 4 attributes you have, then there could be a case for introducing a phone table like this:

    phone table
    phone_id (pk)
    phone
    phone_ext
    phone_type
    ...

    company phone table
    company_id (fk) } (pk)
    phone_id (fk) }

    contact phone table
    company_contact_id (fk) } (pk)
    phone_id (fk) }

    But really that would seem to be "over-design" in your case.

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    Yes Tony,

    My main hangup was the duplication in the two phone tables. I agree with you that adding a common phone table would add more complexity in this case.


    Thanks for your input.
    Julio

Posting Permissions

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