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

Edit: Based on the new comment, where you are going to copy from an address book, to an order_address table, it might keep your order table cleaner to do so, but if you are going to duplicate the data anyway, I would say copy it to the record it belongs too.

--

Both, denormalize the shipping, and keep them with the order. Storage is cheap, and it's easier than managing a bunch of extra data in the address table. But keep the addresses split out so customers don't have to enter them over again. If you denormalize, then you don't have to keep explicit records in your addresses table for them, and worry about doing soft deletes.

Don't underestimate the complexity of managing addresses. If I enter an address into your system, and associate it with my account, and then realize some part of it is wrong, then you need to remove the old one and create a new one. The removal can be a soft delete, but it needs to be removed. You can try to decide if I was putting in a new address, or dramatically changing the old one. Or you can only allow adding and removing addresses. But when operations happen with addresses, the previous orders need to maintain the data that was assigned to it in the first place. Editing an address that is already associated with an order, would modify where the order says it was sent to, after it has already been sent. Make sure you think through these scenarios. There are several ways to solve the potential problems, but your decision is really based on how you want to handle these situations. if you denormalize and copy the address information to the order once it is placed, then editing addresses in the address table becomes less of an issue. Decide how to handle these situations, and your database schema just needs to support that. Either choice works.

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.

Answer 3

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 4

Pulling the addresses into separate tables is more normalized, but be careful. If you allow the addresses to be updated, you can lose track of where the order was originally intended to be billed to / shipped to.

Answer 5

I would keep the addresses in a separate table and reference them from the orders. I would include a "CurrentAddress" attribute, so that an end user can "delete" that address from their list of current address. The value would still exist in the table so previous orders could reference the address for historical purposes, but it would no longer be a selectable address at order time.

Answer 6

The second method has a couple advantages over the first. It is easier simply to have the addresses be the same, as they often will be, with less possibility of error. Also, if you ever save addresses within an account, the second method will give you an easier time. That said, you need to verify that a given address actually belongs to the same account as the order, which you might do by including a customer_id field in both the order and order_address tables, then including customer_id in the primary key of order_address and the foreign key from order to order_address.

Answer 7

A table with the billing and shipping address id will serve better if you plan to register users in your site, then you can place the order in other table and use the ids you already have to correlate data between order info <=> billing/shipping addresses

Answer 8

It depends whether addresses are re-used.

If you have a "registered customer" table, you should definitely go for the option with "delivery_adress", "billing_adress", etc. tables, each record of them being linked to a customer.