CODEWITHSUNDEEP
sqldatabasedatabase-schemahistorical-db
ololoken
ololoken

Reputation: 58

Retaining volatile data in orders

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?

Upvotes: 2

Views: 147

Answers (1)

jontejj
jontejj

Reputation: 2840

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.

Upvotes: 1

Related Questions