Lets assume I have tables: customers and orders, I'd like to store order with unchangeable customer information (like address, name etc.) but do not want to copy all this information to orders table. There are three options:
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
So the question is: what approach is more preferable from different points of view like db design, readability, implementation complexity?
I recommend to something similar to what @Jeffrey L Whitledge suggests in 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.
Every data that can be changed should be grouped, such as address is here, if anything changes in the address, it's easy to generate a new entity and the order row can continue to refer to the old entity.
In data warehouse terms, this is usually called a star schema where you differentiate between fact and dimensional tables.
