SQL 表设计：我应该将地址与订单一起存储还是存储在单独的表中？
我正忙于创建一个基本的电子商务网站，想知道以下关于我存储帐单和送货地址的方式的两个选项中最好的是什么。我愿意接受任何其他建议。
我可以在订单表中包含帐单地址和送货地址：
order
-------
billing_name
billing_address
billing_state
shipping_name
shipping_address
shipping_state
否则我可以创建另一个仅存储订单地址的表：
order
-------
billing_address_id
shipping_address_id
order_address
-------
address_id
name
address
state
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
就我个人而言，我不喜欢你们的两个解决方案，尽管第二个解决方案在数据库理论方面“更正确”。如果您有重复的地址，您应该将它们存储一次。
问题出在执行上。下订单时，您必须决定是要使用现有地址、更新现有地址（例如，使用新添加的公寓号码）还是创建新地址（客户已搬迁） ，有一个新的夏季地址，无论如何）。
为了做到这一点，某人（直接或电话销售的员工、客户或在线销售的程序）必须决定您是执行地址更新还是地址添加操作。让用户准确地做出这样的决定是非常困难的。如果在确实需要添加时执行更新，则您的订单历史记录已损坏（旧订单指向新地址）。如果在更新是正确选择时执行添加，则您已经消除了标准化结构的值。
在这种情况下，我得出的结论并不完全令人高兴，即最好的选择是为客户存储一个或多个地址，然后将地址信息复制到订单本身的地址字段中。
如果您选择第二个选项，则需要计划为地址系统编写一个非常好的用户界面，以避免我上面提到的那种问题。请记住，不仅是您，将来从事该项目的每个程序员都必须理解并同意该地址表的管理。
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.
将地址拉入单独的表中更为规范化，但要小心。如果您允许更新地址，您可能会忘记订单最初的记帐地址/发货地址。
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.
编辑：根据新评论，您将从地址簿复制到 order_address 表，这样做可能会使您的订单表更清晰，但如果您要复制无论如何，我想说将其复制到它所属的记录中。
--
两者都将运输非规范化，并将其与订单放在一起。存储很便宜，而且比管理地址表中的一堆额外数据更容易。但请将地址分开，这样客户就不必再次输入它们。如果您进行非规范化，那么您不必在地址表中为它们保留显式记录，也不必担心进行软删除。
不要低估管理地址的复杂性。如果我在您的系统中输入一个地址，并将其与我的帐户关联，然后意识到其中的某些部分是错误的，那么您需要删除旧地址并创建一个新地址。删除可以是软删除，但是需要删除。您可以尝试决定我是要输入新地址，还是要大幅更改旧地址。或者您只能允许添加和删除地址。但是，当对地址进行操作时，先前的命令需要维护首先分配给它的数据。编辑已与订单关联的地址会在订单发送后修改订单所说的发送地址。确保你仔细考虑了这些场景。解决潜在问题的方法有多种，但您的决定实际上取决于您想要如何处理这些情况。如果您在下订单后将地址信息非规范化并复制到订单中，那么在地址表中编辑地址就不再是问题。决定如何处理这些情况，您的数据库模式只需要支持即可。任何一个选择都有效。
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.
我会将地址保存在单独的表中，并从订单中引用它们。我将包含一个“CurrentAddress”属性，以便最终用户可以从其当前地址列表中“删除”该地址。该值仍然存在于表中，因此以前的订单可以出于历史目的引用该地址，但在订单时它将不再是可选择的地址。
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.
第二种方法比第一种方法有几个优点。让地址保持相同会更容易（通常情况下），并且出错的可能性较小。此外，如果您曾经在帐户中保存过地址，则第二种方法会让您更轻松。也就是说，您需要验证给定地址实际上与订单属于同一帐户，您可以通过在
order和order中包含customer_id字段来实现这一点。 code>order_address 表，然后在order_address的主键中包含customer_id以及从order到order_address 的外键。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_idfield in both theorderandorder_addresstables, then includingcustomer_idin the primary key oforder_addressand the foreign key fromordertoorder_address.这取决于地址是否被重复使用。
如果您有“注册客户”表，那么您绝对应该选择“delivery_adress”、“billing_adress”等表，其中每条记录都链接到一个客户。
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.
如果您计划在您的网站中注册用户，则包含帐单和送货地址 ID 的表会提供更好的服务，然后您可以将订单放入其他表中并使用已有的 ID 来关联订单信息 <=> 之间的数据。帐单/送货地址
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
我通常会选择第二个。这将使您可以为不同类型的客户提供许多不同的地址。但我通常会首先在客户层面解决这个问题，然后再解决订单和发票。
但是，您可能需要解决订单工作流程/业务规则的性质。
订单完成后，它是一份文件（如发票）吗？如果是这样，那么当时的地址应该被锁定，不能更改，否则您可能无法重新出示原始文件。
当客户更改帐单地址时，旧订单的帐单地址还重要吗？在这种情况下，帐单地址甚至不需要从订单链接，只需从客户链接。如果您要重新提交订单进行付款，您需要将其提交到其当前的帐单地址。
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.