Database optimization orders
This is not just about users (and their addresses) but also about prices and other information about products you are selling, that can change after the order has been placed, yet the order itself must remain intact.
Generally, there are 2 approaches to this:
- Copy everything you need in the order (and its items). Even if the "master" data changes, you still have a copy within the order that you can use.
- "Version" or "historize" the whole database, similar to this.
(1) is the more "practical" approach, but can lead to data redundancies (e.g. when address doesn't change, you are nonetheless making separate copies of it).
(2) is the more "purist" approach, but may require more JOINing and generally be more complex.
In general you will most likely want to separate:
- Users
- Addresses
- Order information
This is because users can change address over time, but old addresses need to be kept because they have orders against them. Plus a single user can have multiple orders from the same address, so we separate out this information to reduce duplication.
http://en.wikipedia.org/wiki/Database_normalization