gpt4 book ai didi

sql - 在订单中保留易变数据

转载 作者:搜寻专家 更新时间:2023-10-30 20:34:10 25 4
gpt4 key购买 nike

假设我有表:客户和订单,我想用不可更改的客户信息(如地址、姓名等)存储订单,但不想将所有这些信息复制到订单表。共有三个选项:

a) Mapping table for base customers
orders
....
customer_id; link to the customers table
baseCustomer_id; link to the customers_base table
....
customers
id;
base_id; link to the base customers table;
....
customers_base
id
....

b) Versioning: (if new customer, create version 0 for base customer, and version 1 to have permament record)
orders
....
customer_id
customer_version
....
customers
id
version
....
c) Create a copy of customer info for each order and store it into the same table;
orders
....
customer_id
....
customers
id
....
copy_of; refers to the customers.id if null represents base customer entity

所以问题是:从数据库设计、可读性、实现复杂性等不同角度来看,哪种方法更可取?

最佳答案

我推荐类似于@Jeffrey L Whitledge 在 database-structure-for-storing-historical-data 中建议的东西

Customer
--------
CustomerId (PK)
Name
AddressId (FK)
PhoneNumber
Email

Order
-----
OrderId (PK)
CustomerId (FK)
ShippingAddressId (FK)
BillingAddressId (FK)
TotalAmount

Address
-------
AddressId (PK)
AddressLine1
AddressLine2
City
Region
Country
PostalCode

etc.

所有可以更改的数据都应该分组,比如地址在这里,如果地址发生任何变化,很容易生成一个新的实体,订单行可以继续引用旧的实体。

在数据仓库术语中,这通常称为 star schema区分事实表和维度表的地方。

关于sql - 在订单中保留易变数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49448226/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com