CODEWITHSUNDEEP
sqldatabase-designschema
Ali Adravi
Ali Adravi

Reputation: 22723

SQL separate table for relationship pros & cons

I am working on database design so want to understand the pros & cons of having a separate table only for relationship.

It should be like:

  1. Customer [Customer Detail] (CustomerID AS PK)
  2. Address [Address Detail] (AddressID as PK)
  3. CustomerAddress [CustomerID FK, AddressID FK]

Or

  1. Customer [Customer Detail] (CustomerID AS PK)
  2. Address [Address Detail, CustomerID FK]

A customer can have more than one address.

What are the advantage and disadvantage?

Upvotes: 1

Views: 332

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

This is a reasonable question.

Basically, it boils down to a single question: "Do you want two identical addresses to always have the same key or not?".

In the first version, the "Address Detail" can be unique across the database. So, two room-mates could have the same AddressId. When the Smith's move out and the Jones' move in, they could have the same AddressId.

In the second version, each person would have one or more address records. However, the details for a given address could be repeated.

Which is better depends on your application. Often, the first method is preferable when you are contacting people at the address, because "de-duplication" is built into the data model

Upvotes: 4

Related Questions