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

From: <gmei_at_my-deja.com>
Date: Mon, 14 Feb 2000 18:20:08 GMT
Message-ID: <889h0j$stp$1@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 - 12:20:08 CST