CODEWITHSUNDEEP
sqlrelational-databasedatabase-administration
chrisg229
chrisg229

Reputation: 919

Relational DB Design - Master table with multiple addresses

I need to design a relational DB that will bulk load customer/address data based on periods like Q1, Q2 etc. I am looking to create a simple customer master table that includes demographics such as address. The challenge is that customer addresses MAY change over periods and I need to capture these new addresses and also retain older addresses as a snapshot for earlier periods.

I could easily make customerId/quarter the unique traits for each row but that would cause many unneeded duplicates for customers with addresses that remain the same. Another option could be to make the default quarter be 'ALL' and only add new rows for existing customers with address updates. It would then be up to the query/view to select 'ALL' unless the specified quarter exists (indicating an updated address)

Not sure the optimal approach for such a scenario and I was hoping someone else might have some suggestions?

Upvotes: 0

Views: 602

Answers (1)

bbrumm
bbrumm

Reputation: 1352

I would suggest a design like this:

  • customer: customer ID, other customer fields
  • address: address ID, street 1, other address fields
  • customer_address: customer ID, address ID, valid from date, valid to date

This allows for several things:

  • Addresses to be captured separately to customers. Generally, an address itself doesn't change, a customer's relationship to an address changes. This is captured in the joining table between customer and address.
  • In the customer_address table, you can capture the valid from date and valid to date. This allows you to not only capture that an address is valid for all quarters, but capture changes in addresses for customers. It also allows for quarters over different years (e.g. 1 Jan 2017 and 1 Jan 2018 are both Q1).
  • Having multiple tables like this would reduce the amount of duplicate data in the system.

Your queries could join these tables together and filter based on the valid dates inside the customer_address table.

Does all of that make sense?

Upvotes: 1

Related Questions