Database Layout

[wfernley]

Hello all.

I am building a ECommerce web site for a client of mine and I have hit a snag designing the database. He wants a couple sections in the product catalog like that of StarTech.com's. They have a Where to Buy Section that lists other places to buy the products. They also have a Technical Specifications section. The Tech Specs section has about 20 to 30 entries of various features.

I was curious how I could design the Database for these 2 features. I am using PHP and MySQL.

This is my current idea so please let me know what you think - is it good? will it work? will it slow the site down?

Basically I was thinking of having two tables, a wheretobuy table and techspecs table. The wheretobuy table will have the product_id that will link to the corresponding table and then it will have 15 entries for each - each containing the proper manufacturer. For the techspecs table I would have the product_id that would once again link to the proper product and then have 20 other entries for features, specs, additional specs, and equivalent product codes.

Would this be the best way of designing these two features? If not, how else could I do it?

Thanks in advance for your help too :)

Wes

[Trucker]

Your solution will work. There is one other way that you might consider doing it.

You could create a manufacturers table that includes a mfg_id and all other relevant info about the manufacturer. Then use your wheretobuy table to link the product and mfg IDs together. This might be a better solution if manufacturers are offering more than one product. It's definitely a better solution if you are storing more than just a manufacturer name.

Hope this helps in some way.

[john_k]

From your post, it sounds like you would simply want to list out the various specs for a specific product. If that's the case, then your solution should be okay.

The wording makes it unclear as to whether 20 features will amount to 20 rows in your specs table or if you plan on having one row with upto 20 feature columns. The first of these, a one-to-many relationship is the way to go.

Also, if you will ever want to be able to do a comparison matrix to list features of products A, B, and C side-by-side, then you will need the one-to-many relationship.

One other thing to consider is whether or not the specs will impact the pricing. i.e. will this data be used to create an option list for the user? Be sure to build that ability into the model if it is even a possibility.

[wfernley]

Yes actually that does. :) I was contemplating that too.

Thanks for your reply :)