2

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?

ololoken
  • 58
  • 4

1 Answers1

1

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.

jontejj
  • 2,800
  • 1
  • 25
  • 27
  • Thanks, looks like @Jeffrey L Whitledge claims that the third option is more preferable excepting tracking of direct information about which address was used to create an order entity, replacing it with reference to client. – ololoken Mar 24 '18 at 08:12
  • @ololoken I'm not sure I'm following? I don't see self-referencing entities in his suggestion? – jontejj Mar 27 '18 at 12:33
  • I see that self-reference is inverted to references to new addresses and customer. But once we remove customer from picture then the best option to keep reference to "parent" address is self-reference in addresses table. – ololoken Mar 28 '18 at 12:31