The database I am designing is part of a project I am working on. I have come up with 3 simple “business rules”:
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.
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.
You have addresses in 3 tables. Shrink that down to 1. Then have a
customer_address_id in the
Customers table and
ship_address_id in the
Orders table. Then,
ship_address_id can be nullable. Do not make all the fields in
Addresses Nullable. (Perhaps
post_code can be left blank -- but make them
DEFAULT '' instead of
If a Customer is 'shipping' to himself, simply copy the
One more thing... In order to not lose "old addresses", be careful about when (if ever) you change any row in
Addresses. You may end up with unused entries, but that is a small price to pay for the data integrity you require.
Answered by Rick James on December 31, 2020
0 Asked on December 31, 2020 by jayson-gonzaga
2 Asked on December 28, 2020 by ben-a
2 Asked on December 28, 2020 by yellephen
1 Asked on December 28, 2020 by maheshnagakumar
1 Asked on December 28, 2020 by ivan-c
1 Asked on December 27, 2020 by dragonborn
1 Asked on December 26, 2020 by muon-ha
2 Asked on December 26, 2020 by 3bdalla
1 Asked on December 25, 2020 by beginnerdba
1 Asked on December 25, 2020 by yifan
0 Asked on December 24, 2020 by jabir-baig
1 Asked on December 23, 2020 by kevin-wu
1 Asked on December 23, 2020 by user1973
1 Asked on December 22, 2020
2 Asked on December 21, 2020 by soberuprising
2 Asked on December 20, 2020 by alonzo-hess
1 Asked on December 20, 2020 by karlsc
Get help from others!