更新信息时插入新记录？
我正在开发在线购物车系统，我有 items 和 item_options 表。
每个项目可以有 1 个或多个选项，见下文。
items 表
- item_id (PK)
- item_name
- item_description
注意：商品价格位于 item_options 表中
item_options 表
- option_id (PK)
- item_id (FK)
- option_name
- option_price
当客户下订单时，数据将添加到 order 和 order_items 表中。
订单表
- orders_id（PK）
- FK）
- address_address_id（FK）
- date_purchasedorders_statusorders_date_finished
- customer_id （
order_items表
- orders_items_id（PK）
- orders_id（FK）
- item_id（FK）
- item_option_id（FK）
那里这是一个问题，如果员工更改价格、商品名称或删除商品。客户发票将受到影响，因为 order_items 中的 FK 将不再存在于 items 表中。解决这个问题的办法是什么？
这个解决方案怎么样：在 items 和 item_options 表中添加活动字段。当员工想要更改商品的价格或名称时，只需将当前活动记录设置为 0 (items.active)，然后插入一条包含新信息的新记录，活动记录变为 1。旧订单仍然指向未激活条目的正确 ID。这是好方法吗？
当新信息插入到 items 表中时，这是否意味着我必须更新/更改 item_id.item_options 表中的新 PK ID？
I am developing online shopping cart system, I have items and item_options tables.
Each Item can have 1 or more options, see below.
items table
- item_id (PK)
- item_name
- item_description
Note: item prices are in the item_options tables
item_options table
- option_id (PK)
- item_id (FK)
- option_name
- option_price
When the customer placed an order, the data will be added into the order and order_items table.
orders table
- orders_id (PK)
- customer_id (FK)
- address_address_id (FK)
- date_purchased
- orders_status
- orders_date_finished
order_items table
- orders_items_id (PK)
- orders_id (FK)
- item_id (FK)
- item_option_id (FK)
There is a problem, if the staffs change the price, item name or delete a item.. the customer invoices will be affected because the FK from the order_items will no longer be existed in the items table. What is the solution to this?
How about this solution: add active field in the items and item_options tables. When the staffs want to change the price or name of a item - just turn the current active record to 0 (items.active) and then insert a new record with the new information and active become 1. The old orders still point the correct id of the entry that is not active. Is this good way doing it?
When the new information has been inserted in the items table, does that mean I have to update/change the new PK ID in the item_id.item_options table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在这样的事情上，我绝对反对使用硬删除。每次更新或删除都应该在 item_options 表中添加一个新行，然后将前一行标记为非活动状态（这样用户就不可能选择以前的值之一）。
插入的每个新项目应包含两个步骤：
In something like this, I am definitely against the use of hard deletes. Every update or delete should be adding a new row into the item_options table and then marking the previous one as inactive (that way there is no chance that a user can select one of the previous values).
Each new item inserted should consist of two steps:
我见过的典型解决方案是将您想要历史记录的数据快照存储在单独的表中。这样，您就可以仅存储发票时想要快照的数据，而不是特定表中的所有数据。
当涉及到简化代码中的报告和查询时，它还有助于将历史数据与实时数据分开存储。通常，这两件事不需要同时访问，因此您可以避免更复杂的查询（并且单个表上的负载较重）只是为了访问其中一个。
The typical solution to this that I've seen is to store the snapshot of the data that you want a history of in a separate table. That way you can store only the data you want a snapshot of at the time of the invoice, instead of all data in a particular table.
It will also help to store history data away from live data when it comes to simplifying reporting and queries in the code.. generally these two things don't need to be accessed at the same time, so you avoid the more complex queries (and heavier load on a single table) just to access one or the other.
如果您打算按照您描述的方式执行操作，则必须向 item_options 表添加新行来处理新项目。
If you are going to do things the way you describe, you are going to have to add a new rows to the item_options table to handle the new items.