Store Address Details for Customers & Orders

0
votes

I am struggling with the database design for storing customer address's and relating them to orders in an inventory system. Currently I have the following schema ideas and 3 options:

Customers
CustomerID
CustomerName .....

CustomerAddress
AddressID
CustomerID
AddressType (Billing, Shipping etc)
AddressLine1
AddressLine2 City
State
Country PostCode

Orders (Option 1)
OrderID
CustomerID
BillingAddressID
ShippingAddressID

Orders (Option 2)
OrderID
CustomerID
BillingAddressLine1
BillingAddressLine2
BillingCity
BillingState
BillingCountry
BillingPostCode
ShippingAddressLine1
ShippingAddressLine2
ShippingCity
ShippingState
ShippingCountry
ShippingPostCode

Orders (Option 3)
OrderID
CustomerID
BillingAddressBlob
ShippingAddressBlob

I need the address for an order to remain static overtime. So if I look back at an order two years ago I can see the correct address items were shipped too and billed too.

1
sqldatabase-design

1 Answers

1
votes

Updating the answer since previous answer did not help the user.

You can have a table OrderAddress Table to save address

OrderAddress Table with following columns

  • OrderId
  • AddressId

To link Customer to Address you can have a table CustomerAddress with following columns

  • CustomerId
  • AddressId
  • Street
  • City
  • PIN
  • Phone
  • State
  • Isactive

Isactive column would be used to differentiate which address is currently active. Also, From this table you can see how many different addresses existed for a customer

To find the exact address where order was shipped you can check OrderAddress Table

Hope this soultions meets your need