假设我有表：customers和orders，我想用不可更改的客户信息（如地址、名称等）存储订单，但不想将所有这些信息复制到orders表。有三种选择：
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
所以问题是：从DB设计、可读性、实现复杂度等不同观点来看，哪种方法更可取？
最佳答案
我推荐一些类似于“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.
每个可以更改的数据都应该分组，比如address在这里，如果地址有任何更改，就很容易生成新的实体，order行可以继续引用旧实体。
在数据仓库术语中，这通常被称为star schema，在这里您可以区分事实表和维度表。
关于sql - 按顺序保留 volatile 数据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49448226/