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

Option 3 is used by Amazon. (You can have multiple delivery addresses and choose to which delivery address it should go. In this case it's up to the customer, and you have extreem flexibility)

Why do you want to design UserName as unique. Is that something the customer should enter upon login?

Hth,

Sybrand Bakker, Oracle DBA

<gmei_at_my-deja.com> wrote in message news:889h27$su9$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 - 14:18:15 CST