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

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.

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