1

I am designing a database and initially I setup addresses separate from orders so the orders table just has a billing id and address id reference and the actual address are stored in the address table.

However the more I think about this i am not sure there is any real advantage to putting address into their own table.

The main reason is that a order is self contained meaning that the address is only valid for that order.

Having address linked instead of on the order record leads to some complications when updating addresses. Say if we have two orders from one customer linked to the same address and he wants the address changed on one order but not the other a new address. The application now needs to create a new address in the address table and change the linked address on the order (you can't just change the address because it would change it for both linked orders).

You also have to have code in your application that links new orders to addresses already in the database (and that only works for orders where the addresses are identical or nearly identical).

The only advantage I see right now to having order addresses in their own table is so the billing address and shipping address are not duplicated for every order.

On the surface it seems like preventing billing and shipping address data duplication isn't worth the extra code needed to make storing addresses in their own table work.

Any recommendations on how to handle this? Is there some big advantage to handling the addresses in separate tables that I am missing?

Nathan Arendt
  • 13
  • 3
  • Are you going to support registered customers in the future? If yes then use a separate address table. – The Nail Feb 18 '12 at 17:59
  • Yes and no. There are registered customers one our website, ebay, amazon ect but in the program that handles orders there are no registered customers. Even if there were I am not sure how that changes anything in regard to orders. Since a change to a customers prefered address doesn't necessarily update where they want a order shipped and it doesn't change where any previous orders were shipped. – Nathan Arendt Feb 18 '12 at 18:12
  • Just make sure you do not lose any data, then you'll be fine; as is mentioned in the answers below, make sure that you always keep the address that belongs to an order, even when the address of some customer changes in the meantime. The same holds for things like product prices. – The Nail Feb 18 '12 at 18:18
  • See also: http://stackoverflow.com/questions/307027/is-this-a-good-way-to-model-address-information-in-a-relational-database – WW. Mar 21 '12 at 07:58

2 Answers2

2

It sounds like what you want (ideally) is for the order to be linked to the address as it was at the time of the order.

There are two main ways to implement this - either the order includes all of the address information within its own structure, or you implement the address table as a temporal table, and ensure the order has appropriate datetime information to locate the correct address rows when queried.

Which one you implement depends on how you wish to handle updates - both models may have issues, depending on whether in-flight orders should/should not be updated.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

If the address is linked to that order only, then it should be part of the order, not in its own table.

Unless your database has logic relating to an actual address entity, it doesn't need to be in its own table even if you'll want to add the ability to store address information for other sorts of entities.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343