Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from programmers.stackexchange.com Aug 22 '13 at 21:20

This question came from our site for professional programmers interested in conceptual questions about software development.

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 '13 at 15:50
    
Yikes can't keep track of all these stackexchanges. –  SLC Aug 22 '13 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. –  Robert Harvey Aug 22 '13 at 15:52
    
That would be in addition to the autogenerated unique id –  SLC Aug 22 '13 at 15:53
1  
That would be CustomerID as a foreign key, not AddressID. –  Robert Harvey Aug 22 '13 at 15:53
show 1 more comment

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

share|improve this answer
add comment

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

share|improve this answer
    
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 '13 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 '13 at 15:59
    
Okay, great! Thanks very much :) –  SLC Aug 22 '13 at 15:59
add comment

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

--

Party{
  type [individual, org]
  name
  ...
}

PartyAddress{
  party_id
  address_id
  role [home mailing, business fax, home cell]
}

Address{
  type [web, mailing, email, phone]
}

MailingAddressComponent{
  address_id
  type [street, city, town, hamlet, province, state...]
  value
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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