handling product options that affect price

jennyp

I'm setting up an ecommerce site where PHP code looks up products' prices in a MySQL database and displays them on the page accordingly. The site also includes a CMS where the admin can update the database information (change prices, add new products, etc).

It all works OK, but now I've come up against a conceptual stumbling block concerning price options – the way prices can vary according the user's choice of certain options for a product. For instance, a product may have a different price if in "large" size, or if "with gold finish" – or a different price again if say "extra large" AND "gold finish".

I'd be grateful for any tips and advice as to the rationale of building such options into a database so that they can be (a) retrieved and displayed in pages, and (b) inserted in a 'CMS' so that a user can create a fair number of price options.

At first I thought that one approach would be to have different "products" for each different combination of all the price options for a true product entity, but I thought that this approach would make inserting records in a CMS awkward.

How far is any appropriate method usually tailored to the individual site? Is it always very dependent on the values that are likely to fill the particular database, or are there global approaches that meet many scenarios?

Thanks in advance for any tips or pointers to resources.

Piranha

An idea is to have a table for just the prize like this:

CREATE TABLE product(
    id int primary key,
    name varchar(30) not null
    and so on)

CREATE TABLE prize(
    id int primary key,
    product_id int,
    description varchar(30),
    prize int,
    and so on
    foreign key (product_id) references product(id))

this way you can display the product more or less like this:

Table Kalle
This table is a table that can be bought with a lot of different finishes. To bad that the legs are not included.
Prize:
Standard $30
Gold finish $50
Extra large $60
Extra large with gold finish $70

jennyp

Thanks for the pointer, but I'm confused as to how this would map out out different prices for different options on the same productID..?

Piranha

First: I edited the post above, added a foreign key that I forgot before.

In this case the product table contains all that is the same, for example that it's a table, what materials are used and other stuff. The prize table (maybe should be named options) contains information about each combination of options. They are linked by using a foreign key in the prize table, and that is how to have different options for the same productid. The id in prize is not the same as the id in product.

Of course it is possible to have a base price in the product table and the difference in price in the prize table. That actually may be a better way to solve it if the options always cost the same, no matter if it is combined with other options or not.