How do I sort data in this case?

Hello
I am building my first eCommerce site using MySQL.
This is more of a conceptual question rather than technical.

ProductID 1 (Bolt) - this bolt has 6 different sizes, and a tricky part is only option #6 is in different price.

Option for ProductID 1;
#1 6x35
#2 6x40
#3 6x45
#4 6x50
#5 6x60
#6 6x80
(#1 to #5 are $2, but #6 is $3)

Eventually I'd like to set up the site using eCart. When I do that, I am planning to put dropdown menu so I can save space. And when you choose #6, I'd like the price to be changed as well.

In my limited ability I guessed that an easy way is that separately input #6 as another product, but if possible, I'd like to put this #6 together with others since they are the same products.

I have a feeling this is not what I can do on MySQL, but even so, I'd like to know how to prepare data (in option table) on MySQL so I can eventually make in the way I mentionted. I hope I made my question clear enough...

I really appreciate this forum to solve each questions. Very helpful and educational.

Thank you in advance.
Rika

See if this gives you some ideas:

Creating tables for product options
175/

Hello Office Guy,

Thanks for prompt reply.
I tried to go to the page you suggested, somehow it leads me to the top page of Knowledgebase ( and the site looks very useful :-).
I searched the entry title within the site, still nothing hits.
Then I googled the title, the result comes up, so I click it, but again, it puts me back to the top page...

Maybe the page doesn't exist anymore? I really wanted to read this tho!!!
Any other suggestion?

Rika

Ok, I am getting no suggestion, does this mean that my level is not good enough to get help here??

I did spend 2 days to figure out ways to answer my question.
I see a little light now though, I got an idea of making an independent table for price list ( price was included in product table before) with productID and optionID. So I can call different price depending on which option is chosen??

It would be nice to find out that at least I'm on a right track...

Sorry about the lack of response, the last time I saw this I thought you were involved in a specific conversation so I did not want to but in.

I think the best approach for your options scenario would be to have a table that will have a record for each option, this record would need the option name, the item id to associate it with and a price that will add to the price of the item. I think this is the best way to keep the options and products organized.

On a product detail page you could then have a recordset that selects all of the options for that particular item. You can use this recordset to populate a select list with the options. Getting the price to add to the items price requires a couple more steps though. For the option select list you have should have the option name for the label and maybe also list the additional price for that option in the label as well. For the value you should have the id of the option. Then when the user submits the form to add the item to their cart you will need to have another recordset that selects from your option table and filters based on the posted option id. You can then use the bindings from this recordset to get at the price and name for the option to include in your add to cart. You can add the price to the price for the item and include the name of the option in the corresponding cart column.

Let me know if you have any different ideas for how you would like to implement this or if you have any questions in general about how you can accomplish this.

i think you are on the right track.

if your item price is dependent on size, you should create a table incorporating product_id, size_id and price.

Thank you guys for opinions and idea.

Eric, I will read through again and study what you suggested.
It seems I need to take some time to comprehend your idea... (as English is not my first language, this whole learning process has been taking me double time!!)
I will come back again when I hit a wall and need your help again.

CraigR, I will try your suggestion and see if I can work it out.
(makes me feel so nice just to hear that I'm on right track when I had no clue where I was...!)

Again, thank you, and have a nice weekend.

Rika ;-)