Common Phone table serving two other tables
 

Hi all,

I have a customer table. Each customer can have multiple contacts, as well as multiple phone numbers. Each contact can also have multiple phone numbers.

My design right now is as follows:

customer table:
cust_id
cust_name
cust_address
...

customer contact table
cust_contact_id
cust_id (fk) -> refers to customer table's cust_id
cust_contact_name
...

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

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

customer_phone_table
phone_id
cust_id (fk)
phone
phone_ext

contact_phone_table
phone_id
cust_contact_id (fk)
phone
phone_ext

Is there a better solution that I'm missing?

Thanks
Julio

Re: Common Phone table serving two other tables
 

Hi Julio,

Another way would be to have a single phone number table with two FKs (cust_id & cust_contact_id). Only one FK would be used for each phone number record.

James

Re: Common Phone table serving two other tables
 

I've attached my draft...if anything not clear reply...