Shopping cart db design - accounting for product variations

[raj]

Hi

im designing a database/asp cart that sells tshirts online. at the
moment, each tshirt has a product id which is stored in a table called
session_product along with the session_ID.

The thing I want to know is how to store the colour variation of each
tshirt when a user adds the tshirt to their basket. I dont really
want to have to create a new product id for each colour...

any help appreciated!

[Aaron Bertrand [MVP]]

> im designing a database/asp cart that sells tshirts online. at the

> moment, each tshirt has a product id which is stored in a table called
> session_product along with the session_ID.
>
> The thing I want to know is how to store the colour variation of each
> tshirt when a user adds the tshirt to their basket. I dont really
> want to have to create a new product id for each colour...

Well, you could have a color column, though not all products will have
color. You're also going to have to have a size column, a quantity column,
....

A product database is not a light endeavor... there are many variables you
must take into account, and there will be growing pains I can assure you...

[Don Grover]

I have just finished one for a major company and part of there products are
clothes.
I gave the each product a product number and with products with different
colours / sizes i added an extra coloum with colours in it.

Then for displaying to customers I grouped by product number and provided a
button to display all colours, for this I added an extra form on top that
queried the db for that product number and dynamically built new page with
available colours, .
Then I wrote back to table or array those selected items refering back to
the record number.
This allows for multiple variations in the same product..

Of course items in db are one colour per row.

Regards
Don



"raj" <karwalr@hotmail.com> wrote in message
news:477bcb83.0312021609.3dfea814@posting.google.c om...

> Hi
>
> im designing a database/asp cart that sells tshirts online. at the
> moment, each tshirt has a product id which is stored in a table called
> session_product along with the session_ID.
>
> The thing I want to know is how to store the colour variation of each
> tshirt when a user adds the tshirt to their basket. I dont really
> want to have to create a new product id for each colour...
>
> any help appreciated!

[raj k]

Hmm..i see what you mean. The problem that will arise is that when you
add the product to the shopping cart (session_product table), how do you
know what colour product is added? The product id alone wont give you
this.

The only way I can think of is to have another table called
product_colors which holds a unique Id, productID and colour. The
product table can then have a color column which reference the unique ID
from the product_color table. This ID would also be added to the
session_product table to get the product colour variation..

what u think?



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Jeff Cochran]

On 2 Dec 2003 16:09:10 -0800, [email]karwalr@hotmail.com[/email] (raj) wrote:

>im designing a database/asp cart that sells tshirts online. at the
>moment, each tshirt has a product id which is stored in a table called
>session_product along with the session_ID.
>
>The thing I want to know is how to store the colour variation of each
>tshirt when a user adds the tshirt to their basket. I dont really
>want to have to create a new product id for each colour...

Well, if you do a separate ID for each variation of a product, then
those variations just become attributes of that entity. That makes
table design easier, some coding easier and dramatically increases the
number of SKU's you deal with, and display. If color becomes an
entity, then you have to work out the relationship with your items,
since presumably many t-shirts could be the same color and one t-shirt
could be many colors, a typical many-to-many relationship. That means
you end up with more tables, but better control and representation of
the product.

Normally you'd do this type of many-to-many relationship in three
tables, one for product with ProductID as a primary key, one for color
with ColorID as a primary key and a third to reference them with just
two columns, ProductID and ColorID, and a composite key if you need
it. Your SQL gets a bit more involved but is pretty straight forward
if you specify tables out of habit, and you can use INNER/OUTER JOIN
statements to select the proper products for display.

While you can get really involved in this, most shopping carts would
have a Product ID, a Color ID and a Size ID (as well as quantity,
etc.) stored for each record in a transaction table. This is a pretty
standard product database design for any POS database, whether web or
brick and mortar. If you do some digging for samples you'll find
quite a bit available, and the Northwind database sample in
Microsoft's database products is a great place to start.

By the way, you *should* have all this design work done before you
start working on the coding for display, etc. :)

Jeff

[raj k]

thanks for clarifying what i thought! by the way, what are SKU??

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron Bertrand - MVP]

SKU = product code

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"raj k" <karwalr@hotmail.com> wrote in message
news:e6FdFoauDHA.1872@TK2MSFTNGP09.phx.gbl...

> thanks for clarifying what i thought! by the way, what are SKU??
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Raymond D'Anjou \(raydan\)]

Stock Keeping Unit

"raj k" <karwalr@hotmail.com> wrote in message
news:e6FdFoauDHA.1872@TK2MSFTNGP09.phx.gbl...

> thanks for clarifying what i thought! by the way, what are SKU??
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Paul Woods]

For a good cheap ASP-based shopping cart, take a look at Candypress
[url]http://www.candypress.com[/url].

I think you can download the cart and store front-end for free, which would
show you the schema they use to solve the product variation issue. It seems
to be well done (though I don't claim to be an expert on shopping carts).


"raj" <karwalr@hotmail.com> wrote in message
news:477bcb83.0312021609.3dfea814@posting.google.c om...

> Hi
>
> im designing a database/asp cart that sells tshirts online. at the
> moment, each tshirt has a product id which is stored in a table called
> session_product along with the session_ID.
>
> The thing I want to know is how to store the colour variation of each
> tshirt when a user adds the tshirt to their basket. I dont really
> want to have to create a new product id for each colour...
>
> any help appreciated!

[Jeff Cochran]

On Wed, 03 Dec 2003 06:30:36 -0800, raj k <karwalr@hotmail.com> wrote:

>thanks for clarifying what i thought! by the way, what are SKU??

Stock Keeping Unit. Mostly my retail and Point of Sale (POS)
background peeking through... :)

Your term would be ProductID.

Jeff

[Don Grover]

Display only one product with no color (group) show, instead of allowing
user to eneter qty give them a link to a secondary page that gets pass the
product number to it.
Then enumerate all products that have the passed product number pickup the
color from the products row and build a dynamic page showing colour.
In that page put the colour name and a textbox for quantities with a txtbox
name of the product id for that colour.
I actually used a session array to hold product qty and rproductid between
pages.

Don

"raj k" <karwalr@hotmail.com> wrote in message
news:ONNOHwZuDHA.1596@TK2MSFTNGP10.phx.gbl...

> Hmm..i see what you mean. The problem that will arise is that when you
> add the product to the shopping cart (session_product table), how do you
> know what colour product is added? The product id alone wont give you
> this.
>
> The only way I can think of is to have another table called
> product_colors which holds a unique Id, productID and colour. The
> product table can then have a color column which reference the unique ID
> from the product_color table. This ID would also be added to the
> session_product table to get the product colour variation..
>
> what u think?
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!