Primary Key

[Christo Yssel]

Hi,

I have one of 2 options. Which would be the best to use?

Table: Customers, CustomerID (PK)

Table: Addresses, AddressID (PK)
CustomerID (FK)
AddressType {PA - Postal address, DA - Delivery
Address etc.}

Customers may have more than one address, but only one postal address, one
delivery address.
Should the address table have a PK, AddressID and then an unique index
CustomerID and AddressType or can I use a Composite PK based on CustomerID
and AddressID where you then do not need AddressID as a PK?

Thanks
Christo

 

[Douglas J. Steele]

What happens if you have more than one customer at the same address? Your
design can't handle that possibility. And what happens if customer 1 has the
same postal address as customer 2's delivery address?

Table: Customers, CustomerID (PK)

Table: CustomersAddress, CustomerID (CPK)
AddressID (CPK)
AddressType {PA - Postal address, DA -
Delivery } (CPK)

Table: Addresses, AddressID (PK)

I've introduced an intersection table that has a compound primary key of
CustomerID, AddressID and AddressType.

You might want to add a unique index on that with CustomerID and AddressType
to ensure that no customer can have more than one of each address type.

 

[Christo Yssel]

I should have handled this as a many-to-many relationship in the first
place. This will cover all the bases, even when a customer works from home
where his Residential, postal and delivery addresses are the same etc.

Thanks
Christo