items set up by color

I am doing a shopping cart with over 200 items. Most items are unique but there are several that come in two colors, white and bronze.
Question is should each item be set up individually in the database or with a color table and in the product pages have a list/menu to pick color?
I have not had any shopping carts with color or size choices, but i know there is a standard way most clothing or shoe sites list colors and sizes?

thanks for any help
Jim Balthrop

Are you doing inventory control? Do you use the same SKU in your store for both items? Are there any changes to price or other details besides the option selection?

There is no standard way, there are a variety of ways and it is generally best to use the one that is easiest for you that fits your application.

Most techniques would involve creating a table to store your options and maybe a second table to group your options, then either adding a third table to maintain the relationship between products and options, or if you would never have a product with more than one option you can just add an optiongroupID field directly to the products table.

This is all assuming you aren't doing inventory control, which complicates the database structure, but begins very similarly.

thanks Ray

no not doing inventory control. It is a site that sells replacement parts for screen pool enclosures and patios.
example of item would be an aluminum panel. item is exactly the same except for the color. i do not know for sure yet, but i think the sku would be different.
if that is the case, then the best way would be just a database row for each sku?

I am trying to think about the display of the items and each item would display just a different color.
A shopper would first be looking for the item and then the color.

I am having a hard time explaining it, but i want it to be easy to shop.

Ray

I am not using inventory control. Using ecart4. item database setup and item display is my question.
shopping cart item example is a '5 inch aluminum gutter priced at $10.00 per each'.
Gutter comes in 9 different colors.
My concern is item display. Would like item to show once and a list/menu choice of color.
How to set up colors in database and make it shoppable in shopping cart of eCart4?

thanks for your help,
jim balthrop

you would just add a column to the cart for colors and mark it as unique. That is all you need from an eCart perspective.

In your database it is really up to you. Do products have more than one set of options?

It almost always involves creating an OptionGroup table with things like Size and Color, and an Options table with things like Small, medium, and large.

Then you either have the option group referenced directly from your products table if you only have one option per product, or you have another ProductOptions table to maintain that relationship.

From there it is just a matter of creating a recordset that returns the options on the cart page and displaying those in a list. Dreamweaver offers this functionality, so again it isn't really a matter of getting it working for the cart, it is a matter of getting it working in the catalog and adding to the cart will be the easy part.

thanks again Ray,

I think i get it, so if an item has colors as the only option, you only have to enter the item once in an items table and use an options table for colors and maintain a relationship?

That sounds right. Really you could use any number of database designs depending on how your products work and what you need. Any will work with eCart once you get them into a list, so the only difficult part is designing and populating your database and writing the correct query to create a recordset that you can use to populate that list.

By the time you get to eCart the work is already done.

Ray

could you help me with the database setup?
Now in my item table i have a column, itemCOLORID and a table, 'item color' but that is not right. In the table, item color, it has two columns, itemCOLORID and itemcolorname.
1 = white
2 = bronze
3= royal brown
and so forth

here is my sql query-
SELECT items.itemName, items.itemShortDesc, items.itemLongDesc, items.itemPrice, items.itemThumb, items.itemID, items.itemCatID, items.itemCOLORID, items.itemSKU, itemcolor.itemcolorname, itemcategory.itemCatName
FROM items, itemcolor, itemcategory
WHERE items.itemCatID = 3
ORDER BY items.itemName

i know i need some WHERE statement with a color group, but not sure how?

thanks for your help

It looks like you need to take a step back and do a little more research on database queries... specifically joining tables... I think it might help to go through this tutorial:
158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

thanks Ray
very good tutorial
But even with the correct JOIN, i think my database setup is not correct.

I use the WHERE itemCATID = 3 to go to the products page of all category 3 products.
On that page is where i want a list/menu for each item, where the shopper can pick the color of the product/products to add to the cart. In order to dynamically populate the list/menu with the proper choices of colors for that product, i must have a correct recordset.

So i think in my items table, i need a column of colorgroupID and a color table with colorgroupID saying what colors are available for that colorgroupID.

Am i on the right track?