2

Lets say there is orders table with few foreign keys. One of them is addressId.

Orders
-- Id
-- orderNum
-- customerId
-- addressId (foreign)

CustomerAddress
-- Id
-- customerId
-- street
-- cityId
-- areaId
-- postal
-- geo

Customer
-- Id
-- Name
-- more columns

now when the customer updates the address in profile. the updated address will show up even for past orders that are linked to that record. which is not good.

My thought solution is :-

  1. create a new table (OrderAddress).
  2. when an order is placed, insert from CustomerAddress to OrderAddress.
  3. get addressId of the newly inserted record from OrderAddress and save it in the Order record.

this way I make sure the order address would not get modified in case the original address has been. But it's kind of expensive.

Is this good practice ?

2 Answers 2

4

I would maintain two address tables. Not an address per order. These would be...

  1. CustomerAddress - would always be their home address.
  2. ShippingAddress - This could be anywhere they would like something shipped to, even if it's somewhere other than their home address.

If they ever update their shipping address, simply insert a new record in shipping address and mark it as active or primary. This way all previous orders still link back to shipping address 1, and all future orders link to shipping address 2.

5
  • I like the idea of active flag. Thank you very much for your input Commented Feb 25, 2022 at 20:18
  • You're welcome. Let me know if you have any other questions. Commented Feb 25, 2022 at 20:20
  • In fact I do have. After posting this question, I bumped into Temporal tables. what do you think of this approach in terms of performance ? Commented Feb 25, 2022 at 20:24
  • I don't have a lot of experience with temporal tables. Commented Feb 25, 2022 at 20:27
  • 1
    Off the top of my head, I'd think of that more for auditing or trending. In your case, I'd keep with what I suggested, because you would still have orders that link to an older address. But, maybe someone else could comment more on those. Maybe add mention of it in your original post. Commented Feb 25, 2022 at 20:29
2

I would simply duplicate the street, cidtyId and postal columns in the Orders table and keep the addressId column. This allows you to update these columns from the CustomerAddress table in case only a typo was corrected in the address and the address was not changed completely.

But fundamental questions are:

  • Why does Orders have a customerId as well as an addressId?
  • How is the address linked to the customer? Does the customer have an addressId column or is there a link table between the two?

In case a customer can have several addresses, there should be a customerId foreign key column in CustomerAddress. It would then be possible to only have a customerAddressId in the orders and this would determine the customer as well. Not only the address columns should be duplicated, but also the customer name. Sometimes companies change their name.

5
  • I have updated the question. The customer can have many addresses and can choose which address when placing the order Commented Feb 25, 2022 at 19:49
  • I have linked customerId and addressId in the order for simplifying the select statements Commented Feb 25, 2022 at 19:50
  • how do I detect if it's only a typo correction ? Commented Feb 25, 2022 at 19:51
  • Present the original address as well as the copied fields when they differ. The user can the decide what to do. Commented Feb 25, 2022 at 19:55
  • @OlivierJacot-Descombes I would be careful with saying I would simply duplicate the street, cidtyId and postal columns in the Orders table. There are a lot of potential ramifications with this idea. Each row in the orders table will now be larger, and reduce the number of rows that can reside on one page. More disk space will be used, higher memory grants required, etc. The additional pages will also need to be backed up, checked from corruption, and have index maintenance performed against them. It would be best to use data duplication as a last option. Commented Feb 26, 2022 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.