The database I am designing is part of a project I am working on. I have come up with 4 simple "business rules":

• Customers must either be Individuals or Businesses (a business belongs to a certain type).
• A Customer must place at least one Order.
• Orders must contain their historical shipping address (i.e. if a customer updates their address, any Orders associated with the Customer must reference the old address).
• Some Orders do not require shipping (e.g. software products).

I have come up with the following design based on the above requirements:

However, there is a problem with normalization.
In order for the 3rd requirement to be met, I am saving the shipping address into the Order at the time of creation. Additionally, I am referencing an address in the Customer table (which is potentially the same).

I further made the shipping address fields NULLABLE to take care of requirement 4.

In the bottom right of the image, I extracted the Address-related attributes into its own table as the first step in my attempt to normalise the design.

Over the past week, I have searched for hints without much luck. I know about this post and this one, yet the implementation is rather ambiguous for my scenario/case.

The thought of making a single table with all addresses, then referencing each as required has crossed my mind but doesn't seem like the right (let alone best) way of achieving this goal.

My question is simple:

How do I meet the 3rd requirement of associating an address with a Customer and an Order historically, with normalisation in mind?

Any help is very much appreciated. Cheers.

The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules":

• Customers must either be Individuals or Businesses (a business belongs to a certain type).
• A Customer must place at least one Order.
• Orders must contain their historical shipping address (i.e. if a customer updates their address, any Orders associated with the Customer must reference the old address).

I have come up with the following design based on the above requirements:

However, there is a problem with normalization.
In order for the 3rd requirement to be met, I am saving the shipping address into the Order at the time of creation. Additionally, I am referencing an address in the Customer table (which is potentially the same).

In the bottom right of the image, I extracted the Address-related attributes into its own table as the first step in my attempt to normalise the design.

Over the past week, I have searched for hints without much luck. I know about this post and this one, yet the implementation is rather ambiguous for my scenario/case.

The thought of making a single table with all addresses, then referencing each as required has crossed my mind but doesn't seem like the right (let alone best) way of achieving this goal.

My question is simple:

How do I meet the 3rd requirement of associating an address with a Customer and an Order historically, with normalisation in mind?

Any help is very much appreciated. Cheers.