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

Like Alans reply: Do you need the historical data? Only thing I could see it usefull for would be: Tax reporting for sales in that area, and some OLAP/Demographic type stuff(i.e. how many widgets sold to aliens west of the Mississippi).

One idea to keeping the historical data would be to make the shipto address a "many-to-one" relation to the user table. Then add a boolian column which would indicate 1=current_address or 0=old_address.

Good luch.

Regards,

oracle_man

gmei_at_my-deja.com wrote in message <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 Tue Feb 15 2000 - 00:00:00 CET