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

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,

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