[thelist] DB Structure - Custom Gift Baskets

[Warden, Matt]

thelisters,

I am not a DBA, and a problem has arisen that my little brain can't handle
by itself. So, I thought I'd bounce the problem of y'all and see if I can be
pointed in the right direction.

Here's the situation:
I will have two tables that hold shopping cart data, roughly described
below.

-----------------------------------------------------

_ShoppingCart_
ID                Int         PK
DateCreated       DateTime


_ShoppingCartItem_
ID                Int         PK
ShoppingCartID    Int         FK
ItemID            Int         FK
Price             Int
DateCreated       DateTime

-----------------------------------------------------


Looks okay, right? Well, as it is this cart system can only support items
that are in the item table. I have to also support custom gift baskets.
There will be a separate table that holds possible gift basket items. The
user can mix and match items in a custom gift basket and then add that gift
basket as a *line item* in his/her shopping cart. So, an example shopping
cart printout could be:

Item1                 $5.00
Item2                 $4.00
Custom Gift Basket    $7.34
...

Where "Custom Gift Basket" is a clickable link to the Custom Gift Basket
details, which holds the items in that gift basket. So, it's almost like a
multi-dimensional shopping cart. There can be multiple Custom Gift Baskets
in a single shopping cart, and there could be none as well. I'm not sure how
to implement this on the beckend side, since I really can't store the Custom
Gift Baskets in the Item table, thus relating the Custom Gift Basket to a
shopping cart by ItemID. I thought about table mapping with a generic
foreign key in place of ItemID in ShoppingCartItem, but that really isn't
the best solution. I also thought about ParentID in the ShoppingCartItem
table, but I *really* don't like that idea. But these are the only possible
solutions I can think of. I'm on a really tight schedule and I need to get
this planning stage thought out as soon as possible.

Any help that I can get would be MUCH appreciated.


--
mattwarden
mattwarden.com