[thelist] DB Structure - Custom Gift Baskets

[Anthony Baratta]

Matt...

Here two ideas:

Option one....

create a Custom Table that can be linked "many to one" to the 
_ShoppingCartItem_ table.

e.g. _CustomBasket_

ID                Int
CustomBasketID     Int
ItemID            Int
Price             Int

Then to link the two add a field called CustomBasketID to the 
_ShoppingCartItem_. The CustomBasketID would be created "on-the-fly"

_ShoppingCartItem_
ID                Int
ShoppingCartID    Int
ItemID            Int
CustomBasketID     Int
Price             Int
DateCreated       DateTime

Option Two....

Create a new field in _ShoppingCartItem_ and stuff that with XML describing 
the custom basket.

_ShoppingCartItem_
ID                Int
ShoppingCartID    Int
ItemID            Int
CustomBasketData  Text
Price             Int
DateCreated       DateTime

The XML will hold all the items selected e.g. ItemID, Price, Qty, and other 
info might want later. This basically moves your "one to many" relationship 
between two tables to internal to the _ShoppingCartItem_ table itself.

We use XML embedded into our SQL Database to work with dynamic data about 
ordered items. I think it works really well for us.

----
Anthony Baratta
President
Keyboard Jockeys