Re: DB design question: how to handle address change in online shopping database?

Your choice is driven by the business needs. Do you need to actually keep the historic data, etc...

<gmei_at_my-deja.com> wrote in message news:889h3v$sul$1_at_nnrp1.deja.com...
> Hi:
>
> I am working on a database design of a online shopping database (oracle
> backend with web front end). I am facing a problem of how to handle
> address change. I have simplified the situation here and say we have
> the following tables:
>
> UserTable:
> UserID (PK),
> UserName (Unique Key),
> HomePhoneNo,
> ..
>
> DeliveryLocationTable:
> DeliveryLocationID (PK),
> UserID (FK),
> StreetLine1,
> StreetLine2,
> City,
> State,
> Zip
> .
>
>
> OrderTable:
> OrderID (PK),
> UserID (FK),
> DeliveryLocationID (FK),
> OrderDate,
> OrderStatus,
> .
>
> The problem is that a "User" can move (change "DeliveryLocation"). How
> should this be handled? I can think of a couple of options:
>
> 1.Do a simple sql "update" in DeliveryLocationTable. But doing this we
> will lose the historic info about all the old delivery location. I
> don't think this is a good approach.
>
> 2.Do a "snapshot" of the info when creating the order. This require the
> change of OrderTable structure. The OrderTable looks like:
>
> OrderTable:
> OrderID (PK),
> UserName,
> DeliveryLocationStreetLine1,
> DeliveryLocationStreetLine2,
> DeliveryLocationCity,
> DeliveryLocationState,
> DeliveryLocationZip,
> OrderStatus,
>
> This way the whole history is kept. But there will be lots of
> duplicated data and it takes a lot more disk spaces.
>
> 3.Do not change the OrderTable structure, but does not allow "updates"
> in DeliveryLocation Table. Instead creating a new DeliveryLocation
> record when a user moves. If we do this, we have to have this business
> logic enforce somewhere (either by a trigger or some frond end) and it
> could gets very complicated. I am only using "DeliveryLocation" as an
> example here, it can apply to "UserName" or other data change.
>
> I am leaning toward Option2, what do you think? Any other approaches?
>
> Thanks for you comments.
>
> Guang
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Feb 14 2000 - 00:00:00 CET