How to plan such database with maybe same id?

Question

I have table customer, table seller; and every customer have many phone-numbers, as seller do to. And I decide to do new table: Telephone, with IdTelephone, Prefix, Number, and IdCustomer or IdSeller, i don't know how to identify it, becouse IdCustomer could be same as IdSeller, how to solve this? Mysql...

score 1 · Answer 1 · answered Mar 16 '12 at 09:40

1

a boolean column seller or not

answered Mar 16 '12 at 09:40

Oriesok Vlassky

797
1
13
26

no, seller is a table, with IdSeller:primary_key. Customer is table, IdCustomer is prim key. No booleans – byCoder Mar 16 '12 at 09:47
well I understood that you are planning a third table for telephones for either sellers or customers – Oriesok Vlassky Mar 16 '12 at 09:49

Mikey Hogarth · Accepted Answer · 2012-03-16T09:49:27.197

You could do it like this, where TelephonePerson contains potentially nullable columns for customerId and TelephoneId;

Customer Table:
    Id
    *whatever customer details exist*

Seller Table:
    ID
    *whatever seller details exist*

TelephonePerson:
    SellerId
    CustomerId
    TelephoneId

Telephone:
    ID
    Phone Number

Alterntaively, to avoid the extra table, you could do it like this, where you have linkId and PersonTypeId on your telephone table. The PersonTypeId would refer to either sellers or customers, and based on what that is you join onto the appropriate table on the linkId;

Customer Table:
    Id
    *whatever customer details exist*

Seller Table:
    ID
    *whatever seller details exist*

Telephone:
    ID
    Phone Number
    LinkId
    PersonTypeId

score 1 · Answer 3 · answered Mar 16 '12 at 13:31

You model it with a common person table and if the person is both a customer and a selelr he will be in both tables with the same id. He will also only be in phone, address, etc associated with the personID.

Person:
    PersonID
    *Details common to all people
Customer:
     PersonID
     *whatever customer details exist*  
Sellere:
     PersonID
     *whatever seller details exist*  
TelephonePerson:
     PersonId
     TelephoneId 
Telephone:
     PersonID
     Phone Number

score -1 · Answer 4 · answered Mar 16 '12 at 13:16

The most flexible way might be the following:

First create a neutral phone numbers table.

Phone Numbers:
  # Id
    Phone Number
    Prefix

For each of your contact (seller, customer, whatever), create a N-to-N table.

Sellers
  # Id
    other fields...
Seller Phone Numbers
  # Phone Number Id
  # Seller Id

Customers
  # Id
    other fields...
Customer Phone Numbers
  # Phone Number Id
  # Customer Id

This way:

you can later add a new table using the same pattern
a seller and a customer can both have a common phone number
You get to manage a bit more tables than planned.

How to plan such database with maybe same id?

4 Answers4