If I have a customer that can have many addresses, I can create an Address table with columns Street
, Town
etc. and CustomerId
. Then I can insert multiple records to have multiple addresses per customer.
Alternatively I can create multiple addresses and give them all the same AddressId
, then in my customer table I can have an AddressId
(so you'd do SELECT * FROM Address WHERE Address.AddressId = Customer.AddressId
).
Which of these is better, is there some reason why you'd use one over the other, or is one of them just silly?
Best Answer
This is not a good idea at all. The AddressId supposes to be a primary key in your Address table.
Foreign Key
Use foreign Key relationship between customer and address tables, if customer may have multiple addresses associated to him/her.
Do enforce
foreign key
relationship between customer and its addresses byCustomerId
being aprimary key
in Customer table andforeign key
in Address table. This will ensure that noorphan records
will be left, when customer record is deleted or in-activated.One-to-many relationship diagram:
In the case if same address can be assigned to different customers, aka many-to-many between customer and addresses, do use a third table CustomerAddress (junction table). Junction table should save pair of (CusomerId, AddressId) and any other related information specific for this combination.
For example, in below diagram we have many to many relationship between Orders and products.
For more references on DB design patterns look here.
Many-to-many relationship diagram: