The database I design is part of a project I am working on. I have developed 4 simple "business rules":
- Customers must be either individuals or companies (a company belongs to a particular type).
- A customer must place at least one order.
- Orders must include their historical shipping address (ie, when a customer updates their address, all orders associated with the customer must refer to the old address).
- For some orders no shipping is required (eg software products).
Based on the above requirements, I developed the following design:
However, there is a problem with normalization.
In order for the 3rd requirement to be met, I save the shipping address at the time of creation in the order. In addition, I refer to an address in the customer table (which may be the same).
I also set the shipping address fields to NULL to meet requirement 4.
In the lower right corner of the image, I extracted the address-related attributes into a separate table as a first step in my attempt to normalize the design.
In the last week I searched for clues without much luck.
I know this post and this, but the implementation is pretty unclear for my scenario / case.
The idea of creating a single table with all the addresses and then referring to each address as needed came to mind, but it does not seem like the right (let alone best) way to achieve that goal.
My question is simple:
How do I fulfill the third requirement to historically assign an address to a customer and an order, with a view to normalization?
Any help is greatly appreciated. Cheers.