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?

  • 2
    this is better on DBA (flag for a mod to move), though any time a column with Id in the name that is not unique is a WTF
    – ratchet freak
    Aug 22, 2013 at 15:50
  • Yikes can't keep track of all these stackexchanges.
    – SLC
    Aug 22, 2013 at 15:52
  • 1
    Alternatively I can create multiple addresses and give them all the same AddressId -- Why would you do that? Each address needs a unique ID. Aug 22, 2013 at 15:52
  • That would be in addition to the autogenerated unique id
    – SLC
    Aug 22, 2013 at 15:53
  • 1
    That would be CustomerID as a foreign key, not AddressID. Aug 22, 2013 at 15:53

3 Answers 3


Alternatively I can create multiple addresses and give them all the same AddressId.

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 by CustomerId being a primary key in Customer table and foreign key in Address table. This will ensure that no orphan records will be left, when customer record is deleted or in-activated.

One-to-many relationship diagram:

enter image description here

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:

enter image description here


the second option (addressId that are not unique) is actually the same as using customerId in Address with the only difference that customer needs another column

on this alone I would just use the foreign key relationship with customerId

  • That makes sense. Is there any loss of transparency? If you select * from customer you can't see a link to the Address table - is that normal?
    – SLC
    Aug 22, 2013 at 15:56
  • @SLC most 1 to many relationships are marked by the many-side having the key to the 1-side and the 1-side not having anything related to the many-side
    – ratchet freak
    Aug 22, 2013 at 15:59

A few things:

  • An address can exist but no one lives there
  • A person can have multiple addresses (home, job, other job, summer cottage)
  • A company or person could be at an address
  • Two or more people can share the same address
  • Mom could live at Address1 and Dad could both work and live at Address1
  • Global mailing addresses are complicated. You should break them up into components.
  • You might want to abstract many kinds of addresses (web, email, telephone, mailing) into one supertype, so that you can point a foreign key to any kind of address. For example, you might want to ship an order line to an email address for a digital good, and another order line to ship to a mailing address.

So you need to know about the Party Model, so that you can abstract people and organizations, and you need to know about Table Inheritance, so that you can do abstraction in a database.

Your data model should look like this:

Party -< PartyAddress >- Address -< MailingAddressComponent


  type [individual, org]

  role [home mailing, business fax, home cell]

  type [web, mailing, email, phone]

  type [street, city, town, hamlet, province, state...]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.