SQL table design: Should I store addresses with orders or in separate table?

Question

I am busy creating a basic ecommerce website and would like to know what is the best of the following two options regarding the way I store the billing and delivery addresses. I am open to any other suggestions.

I can included the billing address and delivery address in the Order table:

order
-------
billing_name
billing_address
billing_state
shipping_name
shipping_address
shipping_state

Otherwise I can create another table that will just store addresses for orders:

order
-------
billing_address_id
shipping_address_id

order_address
-------
address_id
name
address
state

Answer 1

I would usually choose the second. This will let you have many different addresses for a customer of different types. But I would normally address this at the customer level first, then address the orders and invoices.

However, you may need to address the nature of your order workflow/business rules.

Once an order is completed, is it a document (like an invoice)? If so, then the address should be locked in at that time and cannot be altered, otherwise you may not be able re-present the original document.

When a customer changes their billing address, does the billing address of an old order even matter anymore? In this case, the billing address does not even need to be linked from the order, only from the customer. If you were to re-present the orders for payment, you would present them to their current billing address.

Answer 2

Personally, I like neither of your solutions although the second solution is "righter" in terms of database theory. If you have repeating addresses you should store them once.

The problem comes in implementation. When an order is placed, you are going to have to make a decision whether you want to use an existing address, update an existing address (for instance, with a newly-added apartment number) or create a new address (the customer has moved, has a new summer address, whatever).

In order to do this, someone (an employee for direct or phone sales, the customer or the program for on-line sales) will have to make a decision as to whether you're performing an address update or address addition operation. It's very difficult to get users to make this kind of decision accurately. If an update is performed when an addition was really needed, you've corrupted your order history (the older orders point to the new address). If an addition is performed when an update was the correct choice, you've eliminated the value of the normalized structure.

In situations like this I've come, not entirely happily, to the conclusion that the best option is to store one or more addresses for the customer and then copy the address information into address fields in the order itself.

If you choose your second option, you need to plan on writing a really good user interface to the address system to avoid the kind of problems I mentioned above. And remember that not only you, but every programmer who works on the project in the future is going to have to understand and agree on the management of that address table.