How to maintain customer addresses for Orders?

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:

enter image description here

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.

Database Administrators Asked by ProGrammer on December 31, 2020

1 Answers

One Answer

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 region and/or post_code can be left blank -- but make them DEFAULT '' instead of NULLable.

If a Customer is 'shipping' to himself, simply copy the customer_address_id into Orders.ship_address_id.

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

Add your own answers!

Related Questions

HAPROXY for Postgres Failover Master/Replica

0  Asked on December 31, 2020 by jayson-gonzaga


Error 0xc000007b with Oracle 19c

2  Asked on December 28, 2020 by ben-a


Join to tables dynamically depending on data

2  Asked on December 28, 2020 by yellephen


PostgreSQL exits the shell when importing large data

2  Asked on December 26, 2020 by 3bdalla


Spike and time out errors every x th minute

1  Asked on December 25, 2020 by beginnerdba


Oracle RAC and DataGuard configuration

1  Asked on December 25, 2020 by yifan


Segragating First LSN and Last LSN from Output MySQL

0  Asked on December 24, 2020 by jabir-baig


Lossless Join Decomposition Criteria

1  Asked on December 23, 2020 by kevin-wu


Mongodb Upgrade from 1.8.2 to 4.4

0  Asked on December 23, 2020 by amanullah


SQLite database becomes corrupt

1  Asked on December 22, 2020 by jkozma


How do I export a multi table database with exceptions?

2  Asked on December 21, 2020 by soberuprising


MS SQL login periodically doesn’t work

2  Asked on December 20, 2020 by alonzo-hess


PostgreSQL: Find row with JSON object attribute in array

1  Asked on December 20, 2020 by karlsc


Hotel booking schema

1  Asked on December 20, 2020 by rakoun


Ask a Question

Get help from others!

© 2022 All rights reserved.