foreign key table update impact



  • 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 ?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2