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

Question

I am developing a basic e-commerce website and would like to know what is the best of the following two options regarding how to store billing and delivery addresses. I am open to any other suggestions.

I can include the payment 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 just saves the addresses for orders:

 order ------- billing_address_id shipping_address_id order_address ------- address_id name address state 

Answer 1

I would choose the second. This will allow you to have different addresses for different types of clients. But I would usually turn to this at the customer level first, then turn to orders and invoices.

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

As soon as the order is completed, is it a document (for example, an invoice)? If so, then the address must be blocked at this time and cannot be changed, otherwise you will not be able to resubmit the original document.

When a customer changes his billing address, does the billing address of the old order still matter? In this case, the billing address does not even need to be associated with the order, only from the client. If you re-submitted payment orders, you will submit them to their current billing address.

Answer 2

Personally, I don't like any of your solutions, although the second solution is “faster” in terms of database theory. If you repeat addresses, they should be stored once.

The problem arises in implementation. When the order is placed, you have to decide whether you want to use the existing address, update the existing address (for example, with the apartment number added) or create a new address (the client has moved, has a new summer address, whatever).

To do this, someone (an employee for direct or telephone sales, a client or an online sales program) will have to decide whether you are updating the address or the operation of adding addresses. It is very difficult to get users to make this decision exactly. If the update is performed when the addition is really necessary, you have corrupted your order history (older orders point to a new address). If the addition is performed when the update was the right choice, you excluded the value of the normalized structure.

In such situations, I came, not very happily, to the conclusion that the best option is to save one or more addresses for the client, and then copy the address information into the address fields in the order that is needed.

If you choose your second option, you need to plan to write a really good user interface for the address system in order to avoid the problems that I mentioned above. And remember that not only you, but every programmer who works on the project in the future will need to understand and coordinate the management of this address table.

Answer 3

Edit: According to the new comment in which you are going to copy from the address book to the order_address table, this may lead to your order table becoming cleaner, but if you are going to duplicate the data anyway, I would say copy it to the record to which it belongs.

-

Both, denormalize delivery and keep them in order. Storage is cheap, and it's easier than managing a ton of extra data in an address table. But keep addresses separate so customers don't have to enter them again. If you denormalize, then you do not need to store explicit entries in your address table for them and worry about doing soft deletions.

Do not underestimate the complexity of address management. If I enter the address into your system and associate it with my account, and then I realize that some part of it is incorrect, you need to delete the old one and create a new one. Removing may be a soft removal, but it needs to be removed. You can try to decide if I inserted a new address or abruptly changed the old one. Or you can allow add and remove addresses. But when operations occur with addresses, previous orders must support the data that was assigned to him in the first place. Editing the address already associated with the order will change where the order reports that it has been sent after it has already been sent. Make sure you think through these scenarios. There are several ways to solve potential problems, but your decision is really based on how you want to deal with these situations. if you denormalize and copy the address information into the order after it is placed, then editing the addresses in the address table becomes less of a problem. Decide how to handle these situations, and your database schema just needs to support it. Any choice works.

Answer 4

I would save the addresses in a separate table and refer to them from orders. I would include the attribute "CurrentAddress" so that the end user can "remove" this address from his list of the current address. The value will still exist in the table so that previous orders can refer to the address for historical purposes, but it will no longer be a selectable address at the time of order.

Answer 5

Pulling addresses into separate tables is more normal, but be careful. If you allow updating addresses, you may lose information about where the order was originally intended to be invoiced / sent.

Answer 6

The second method has several advantages over the first. It’s just simpler that the addresses are the same as they often are, with less chance of error. In addition, if you have ever saved addresses in your account, the second method will give you an easier time. However, you need to make sure that this address really belongs to the same account as the order you can do by including the customer_id field in the customer_id and order_address , and then including customer_id in the primary key order_address and the foreign key for order before order_address .

Answer 7

It depends on whether the addresses will be reused.

If you have a "registered customer" table, you definitely need to go to the option with the tables "delivery_adress", "billing_adress", etc., each record of which is associated with the client.

Answer 8

A table with a billing and delivery address identifier will be better if you plan to register users on your site, then you can place an order in another table and use the identifiers that you already have to compare the data between the order information <=> billing / delivery addresses