database setup where price depends on size and color,style are options.

Hi Jason, I read one of your answers about product options but it seems it did not apply to my problem. My setup is as follows:
in the PRODUCT table I have products whose price depends on the size.I have three catagory products. WEFTED HAIR, PREBONDED HAIR and WIGS. Below I have listed the details. All product categories have different colors and different styles. So in the detail page I have three dropdown lists where the customer choose the product size, product color and product style.
How do you setup the database for such products in the OPTIONS table, OptionGroup and ProductOptions? Should I disregard the OPTIONS,OPTIONGROUP and PRODUCTOPTIONS table and just setup only Product table to simplify?
I have tried with all products in 20 inch size as one products but I could not keep track the ProductStock,color and style.
I am really stuck with this and appreciate a guidance on this.

The WEFTED HAIR, PREBONDED HAIR and WIGS all have the same data structure.I have attached the structure of the product table.

No, you should still use the options, optiongroups and productoptions tables.


in the options groups table, you will enter the names for the options groups; color, size and style.


the options table will contain the options available for each group.


The product options table is used to relate the options to the product.


see our tutorial here:
product_options.php

Jason,

I have looked the tutorial but it assumes I populate my database first and also the tutorial assumes I use eCart 5. I have eCart 4. let me re-phrase my problem:

1. How do I set up my database with the following products and
2. stock inventory is uppdated authomatically after checkout. Note one product have 3 options: color,size,style. For example a customer can buy a product with color= black, size
20" and style=straight.

I sell hair extension and other beauty products. The problem is with hair extension category since each of the three products listed below has size,color and style options.

Hair extension
1. wefted hair
2. prebonded hair
3. wigs

- each of the above 3 products have three options: COLOR, SIZE and STYLE.
- Price depends only on size.
- have 20 different colors(black, dark brown, medium brown, light brown, pale blond,....)
- have 7 sizes (12", 14"...24")
- have 4 styles (straight, deep wave,french-curl,loose wave )

I would like to know if your sample database can handle this problem. If your database can not handle this do you have any other solution?
This is my first webshop since I bought your Webassist product. A ticket might help since I never used benefited a ticket support before.
An attached file with sample tables of PRODUCT,OPTIONS,OPTIONSGROUP,PRODUCTOPTIONS would really help.

yes, the eCommerce database would accomplish this.

The tables use Primary key and Foreign Key column to relate to one another

In the products table, the ProductID is the Primary key, the product data would look like:

ProductID | ProductName etc
1 | wefted hair
2 | prebonded hair
3 | wigs


The optiongroups table uses the OptionGroupID as the primary key. the options groups data would look like:
OptionGroupID | OptionGroupName
1 | Color
2 | Size
3 | Style

The Options table uses the OptionID as the primary key and the OptionGroupID to relate the option to the option group. To add the color options, the OptionGroupID, will use the value 1, size will use the value 2 and style will use 3 :
OptionID | OptionGroupID | OptionName
1 | 1 |Black
2 | 1 |Dark Brown
3 | 1 |Medium Brown
4 | 1 |Light Brown
5 | 1 |Pale Blond
6 | 2 |12 inch
7 | 2 |14 inch
8 | 2 |24 inch
9 | 3 |straight
10 | 3 |deep wave
11 | 3 |french-curl
12 | 3 |loose wave


the productoptions table is used to tie the option to the product and add a price increment if the options will add to the base price set in the product table.

The OptionID is the primary key. ProductID is a foreign key relation to the product table. optionID is a foreign key relation to the Options table and OptionGroupID is a foreign key relation to the option groups table.

ProductOptionsID | ProductID | OptionID | OptionGroupID | OptionPriceIncrement
1 | 1 | 2 | 1 | 0

the example above uses:
productID 1 which is wefted hair.
OptionID 2 which is Dark Brown
OptionGroupID 1 which is color
OptionPriceIncrement 0

ProductOptionsID | ProductID | OptionID | OptionGroupID | OptionPriceIncrement
2 | 2 | 7 | 2 | 5.95


the example above uses:
productID 2 which is prebonded hair
OptionID 7 which is 14 inch
OptionGroupID 2 which is size
OptionPriceIncrement 5.95 This will be added to the base price set in the product table.

Jason,

I think it will not work as suggested because if we take the samples:

ProductOptionsID | ProductID | OptionID | OptionGroupID | OptionPriceIncrement
1 | 1 | 2 | 1 | 0

the example above uses:
productID 1 which is wefted hair.
OptionID 2 which is Dark Brown
OptionGroupID 1 which is color
OptionPriceIncrement 0

ProductOptionsID | ProductID | OptionID | OptionGroupID | OptionPriceIncrement
2 | 2 | 7 | 2 | 5.95


the example above uses:
productID 2 which is prebonded hair
OptionID 7 which is 14 inch
OptionGroupID 2 which is size
OptionPriceIncrement 5.95 This will be added to the base price set in the product table.

for the first sample: 1 | 1 | 2 | 1 | 0 style is unknown
for the second sample : 2 | 2 | 7 | 2 | 5.95 color and style is unknown.

I have tried to fill the PRODUCTOPTIONS and could not get all the combinations.
Also on a related matter, the PRODUCTSTOCK column in the product table can't differentiate stock-wise a wefted hair,dark brown, 12", loose wave " from say
wefted hair,dark brown, 14", straight. They should not be the same product if we need to keep inventory AVAILABLE/NOT AVAILABLE.

It seems if we could manage to have a table like PRODUCTSOPTIONS to list all the combinations of productsID and options in one record(row) , we could add PRODUCTSTOCK column
there.
or may be I am missing something?

each row in the ProductOptions table assigns 1 option to the product.


you need to add rows to the product option table to assign all of the available sizes, colors and styles for each product.

I was merely giving a sample of how One of the available options gets assigned to the product.


If your product stock is option dependent, then you will need to create a separate table to account for the stock for each available product combination.

Jason,

I got it now! Thank you so much. So now each row in the PRODUCTOPTIONS selects only one color to be associated with the PRODUCTS table. So if I need to choose say
Wefted hair | 12" | jet black | straight

I have to select 3 rows from the PRODUCTOPTIONS table to specify the above selection.
productOptionID | productID | optionID | optionGroupID | optionPriceIncrement
1 |1|1|1|0 here only color(jet black) is selected
20|1|20|2|0 here only size(12") is selected. OptionpriceIncrement is 0(price depends on size and 12" has the base price 200 which is listed in the product table)
26|1|26|3|1 here only style(straight) is selected.

For the inventory product stock, which is option dependent, how would you suggest its fields(columns) look like? so far I know that it must have inventoryID, and a foreign key to connect it to at least one table. which table? will the inventory table be only for the option-dependent products or for all products? after the creation of the inventory table, will eCart handle the webshop or there will be a need for major PHP coding?I think you know the problem I am facing.

in your case, each product is comprised of

ProductNumber
-Size Option
-Color Option
-Style Option

your inventory table would need to take each combination into account:

stockID - primary key
stockProductID - foreign key relation to the product
stockSizeID - foreign key relation to the size option
stockColorID - foreign key relation to the color option
stockStyleID - foreign key relation to the style option.
stockQTY - quantity of item in stock.


eCart will automatically set up code to update the inventory when the transaction goes through, you would need to hand code this logic.

Jason, am I creating 3 more tables besides inventory table since I do not have sizeID, colorID and styleID. ?
another thing, what happens to those products which do not have options, will it be enough to list them in the product-table with their respective inventory only give in the main productStock?

no, do not create three more tables, the would relate to the corresponding recod in the options table.