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

In article <889h3v$sul$1_at_nnrp1.deja.com>,   gmei_at_my-deja.com wrote:
> 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.
>

Guang,

It's difficult to comment without more information, but I would consider dropping the UserId foreign key in DeliveryLocationTable completely.

You could use the links from DeliveryLocationTable and UserTable to OrderTable to keep the history (unless you allow delivery address changes within a single order).

You can always copy the link from a previous order if you want to minimise user input on the second order for the same customer.

If you can change delivery addresses for an order, and you want to keep the history, you need to create a new table (called something like "User DeliveryAddresses") to store the use of an address by a user (i.e. have foreign keys to DeliveryLocationTable and UserTable).

I would store some sort of effective dates on that table, to explicitly store the history.

Hope this helps.

--

Cheers,
     Tim


Sent via Deja.com http://www.deja.com/
Before you buy.