Acsess database design advice?

[btn]

Hi. Working on a e-commerce site, using ASP/Acsess/Vbscript. This is
prehaps a database organize question. I use an unique ID for each order in
the cart like this <%=Session.SessionID%>. This works fine and when checkout
all order details are stored in my database table called cart. If there is
several products in the cart, each product is stored in the database with its
own line like the example below. OrderID PName 123456
testproduct1 123456 testproduct2 and so on... I want to store all
products in the same line so that the OrderID is displayed only once in the
database and holds all info on the particular order Like this: OrderID
PName 123456 testproduct1, testproduct2....and so on. Any suggestions
for this? Bjorn.

[Jamesy]

ok two things here.

using the sessionID as a unique OrderId is not 100% fool proof.. if the
server is ever reset or goes down, the sessionID's will also reset and then
you may get the same orderid for more than one order.

I recommmend you simply use the autonumber feature in access for your
OrderId's then you know they will be unique.

ok second thing.. you need a three tables (at least).

table 1 (ORDERS) , holds info like the unique orderid, the customerid,
possibly shipping info etc.

table 2 (ORDERDETAILS) has a autonumber for each record, also holds the
orderID as a forgiegn key and then holds each product ID.

table 3 (PRODUCTS) holds all of the products, each with , yes you've guessed
it, an autonumber for each product

example

ORDERS
OrderID CustomerID Shipping......
43(auto) 3 etcetc....


ORDERDETAILS
OrderDetailID OrderID ProductID Qty
1(auto) 43 5 6
etc etc

PRODUCTS
ProductID ProdName etc
5(auto) Bouncy Ball etc etc....


Hope this makes sense.

James





"btn" <webforumsuser@macromedia.com> wrote in message
news:cvkan1$pu6$1@forums.macromedia.com...

> Hi. Working on a e-commerce site, using ASP/Acsess/Vbscript. This is
> prehaps a database organize question. I use an unique ID for each order
> in
> the cart like this <%=Session.SessionID%>. This works fine and when
> checkout
> all order details are stored in my database table called cart. If there
> is
> several products in the cart, each product is stored in the database with
> its
> own line like the example below. OrderID PName 123456
> testproduct1 123456 testproduct2 and so on... I want to
> store all
> products in the same line so that the OrderID is displayed only once in
> the
> database and holds all info on the particular order Like this: OrderID
> PName 123456 testproduct1, testproduct2....and so on. Any
> suggestions
> for this? Bjorn.
>