Product variations

[palace]

I'm trying rework our company shopping cart.

It needs the ability to accept variants on the products. However these will not be fixed as they would be for say clothing sizes.

It is for a confectionery site. Say you have "lolly x" (replace with your favorite lolly)
lolly x comes in:
1kg bag at $8
500gram bag at $4
and 250gram bag at $2

Then "lolly y"
Comes in:
500gram at $4
and 250gram at $2

But then there comes a change and "lolly x" now also gets a 100gram at $1 which comes in a canister

How would you design this?

I have been thinking of having the following tables (each having a primary key)

Category (chocolates, nuts, other products)
Product (product that fit within the category)
Weight (1kg, 500 gram, etc)
Packaging (Bags, Canisters, whatever other packaging product come in)

Am I on the right track here?

The next challenge will be to pull this all together to display as an order form on the site. (Also the webhost has an older version of mysql so I can not use foreign keys I believe)

[448191]

Pricing:

| id | product_id | quantity | measure_id | packaging_id | price |

Replace weigth with measure. This way you can simply remove and add pricings .

[Jenk]

It's better to be as ambiguous with variations as possible. For bags of flour you might have different weights, but what about food colouring? You may have different size bottles, but what about colours?

Use a grouping identifier (e.g. 'groupId'..) to group variants of the same products together.

[palace]

[quote author=Jenk link=topic=115834.msg471905#msg471905 date=1164187137]
It's better to be as ambiguous with variations as possible. For bags of flour you might have different weights, but what about food colouring? You may have different size bottles, but what about colours?

Use a grouping identifier (e.g. 'groupId'..) to group variants of the same products together.
[/quote]

Maybe it's just me but I can't picture this.

I've attached an excel sheet with the contents of the current database tables (now not only is the current database a huge mess, so is the coding behind the scenes of the site, another story)

I've also included a snapshot of the site showing how it is currently being pulled out and displayed.

I don't know if it's the way data is currently pulled out that's the problem or the design of the database (i suspect the later) but right now it only allows for two variants on a product, anymore and it's not possible.

I'm new to all this and feel looking at broken and messy code that I have going on behind the scenes is doing nothing to help me understand how it should really be done. It sure is giving me a headache though. It's a miracle the order form works at all right now. Though I think (or hope) once I can get my head around the design that I need coding it will be a breeze in comparison.


[attachment deleted by admin]

[Jenk]

The products table:

id | title | description | groupId | categoryId | price

categoryId for "sweets", "popcorn", "dipstix"

groupId for each type of the above.

e.g. all acid drops will have, say, groupId = 1, but there will be two Acid Drops records.. 1 for the 1/2kg bag, another for the 1kg bag.