Adding different size and price options to an online store database

turtleton09

Hope someone can help with this.

At the moment I have an online store all up and running that sells oriental rugs, but based on the assumption that each design is available at a single size and a single cost.

The site has an admin section where the site owner can add new designs using a fairly simple insert form, filling in various details including size and cost, which adds a new record in a simple 'Carpets' table.

What would be the best practice to add in the ability to specify different sizes, with different prices? I'm presuming I'm going to need at least one new table, ie split it out into Carpets and Carpet_Sizes perhaps?

Carpets
Carpet_ID
Design_Ref
etc

Sizes
Carpet_ID
Size
Price

With a query Carpet_Sizes :

Carpet_ID, Design_Ref, Size, Price
1, ABC1, 6x4, 599
1, ABC1, 2x3, 199
1, ABC1, 5x4, 399
2, ABC2, 6x4, 899
2, ABC2, 2x3, 599
3, ABC3, 5x4, 799
3, ABC3, 6x8, 999

What would be the easist way to allow the owner to add this info when adding new designs? Using checkboxes perhaps?

Roger_Ramjet

You are nearly there, just need to add a carpet size id to that table.

What has happened is that the size table is now your main products catalogue table and needs a unique id. This will simplify many things further on. The carpets table has now become a group table that holds additional data common to several products. The data input screen should now be set up with a selection list to choose the carpet group as you add each new size and price.

If the sizes and prices were very standardised then you could get away with adding a new style and selecting the sizes it was available in, but that then makes inventory difficult.

So just treat each size as a separate product item and the style as what it is, part of the description. After all you will want to be able to track how many of each size are on hand, sold, need reorder, etc and also ship and bill correctly: shipping costs must vary with size even if you don't charge that way.

turtleton09

Cheers - although I've still not quite got my head around how it all works on the product detail page where a customer adds an item to the basket. Fortunately at least all the shipping is free / included in the price, so that's not complicating it.

The store currently looks like :

http://www.oriental-chamber.co.uk/persian.php

Presumably something like a checkbox for each size with the price, and just the one add to cart button, which adds the correct product size and price.

But I'm far less sure how to achieve that.

turtleton09

If I have the two tables, what input fields would I have on the page for adding designs?

All the ones from the Carpets table (except the carpet_ID as it's an autonumber), and then what exacly for the different sizes prices?

Or... do I have a page where I add the records for the carpets table....

and a separate page where I add the records for the carpet sizes, with the fields :

Carpet_ID
Size
Price

and so any records from the Carpets table with a matching Carpet_ID in the CarpetSizes will be pulled through OK?

Roger_Ramjet

Each different size of any style is a separate product, just as 1/2 inch bot is a separate product from a 1 inch bolt, but both are in the category 'bolt, sub-category 'Whitworth thread'. The same with your carpets, category 'Persian', sub-category 'Style RR750', item 2'x3'. So you set up the categories then add the items.

turtleton09

OK - so in the first instance I need to keep the existing page for adding records to the carpets table, but dropping the size and price fields?

Is it possible to add the different items with different sizes and prices on the same page, or do I need to do it as a separate entity, entering a new record for each in the CarpetSizes table, entering the CarpetID for each matching design to tie them up?