Forum Moderators: open
I'm working on a project and doing some database design. The idea is to build a website which has products that belong to certain categories. I'm having trouble understanding how to do the database design.
Here when you select a category say "Televisions", then you select a type of television like Plasma and then it gives u several options to refine your search like by "Brand","Price","Screen Size", "Frequency" etc.
Now should I just have a category table with a design like
categoryID ¦ parentID ¦ Description
1 -------------- 0 ----- Electronics
2 -------------- 1 ----- Televisions
3 -------------- 1 ----- Cameras
4 -------------- 2 ----- LCD
5 -------------- 2 ----- Plasma
5 -------------- 2 ----- Tube
And in the products table, store the other details like brand, screen size, frequency etc. But again options like screen size, frequency only apply to television category - for digital cameras you have other options like resolution etc. So how are these options stored. I cant make them part of a products table, because each category will have different options.
Then again if brand is part of the product table, then if a user is browsing a category like
Electronics -> TV -> Plasma -> Sony
Show him all the Sony Plasma TV's. When I look at this tree like structure - I feel the brand name should also be a sub category.
I mean I'm kind of lost here as you can see, so if anyone can point me in the right direction or towards a case study which involves building a similar data structure - I would really appreciate it.
Thankyou sooo much
Sarah
[edited by: jatar_k at 8:24 pm (utc) on Dec. 12, 2007]
[edit reason] no urls thanks [/edit]
Consider that any item will have any number of options, and those options can be anything. New options can come up at any time as technology changes. Additionally, the options can be optional add-ons that change the price, or the options can be required options, that may or may not change the price.
So you have this:
products
record_id¦product_id¦category¦item_code¦title¦price¦ ....
options
record_id¦option_id¦product_id¦option_name¦ option_value¦required¦price¦ ....
So you can now
1. Create an unlimited number of options that are required to select an item without changing the price. For the option "color," you would have three rows in the table for a given product, it would be required, and the price would be 0, so your programming would use the base price:
options
1¦1¦123¦Color¦Red¦1¦0.00¦ ....
2¦2¦123¦Color¦Green¦1¦0.00¦ ....
3¦3¦123¦Color¦Blue¦1¦0.00¦ ....
2. Add unlimited options that ADD to the price, but use the base price of the product and add option prices to that:
products
2¦124¦1¦XA124¦Widgets¦15.00¦ ....
options
4¦4¦124¦Widget Add-On 1¦NULL¦0¦5.00¦ ....
5¦5¦124¦Widget Add-On 2¦NULL¦0¦10.00¦ ....
6¦6¦124¦Widget Add-On 3¦NULL¦0¦15.00¦ ....
Widget with add-on 1 = 15.00 + 5.00 = 20.00, etc.
3. Create options that change the price based only on the options. In this case the base price is 0 and is ignored, and the option is required:
products
3¦125¦1¦XA125¦Variable Widgets¦0.00¦ ....
options
7¦4¦125¦Size¦Small¦1¦5.00¦ ....
8¦5¦125¦Size¦Medium¦1¦10.00¦ ....
9¦6¦125¦Size¦Large¦1¦15.00¦ ....
The product manufacturer, category, and other tables would of course be relational, allowing that data to change easily without having a wide row on products.
The programming and select statements required to correctly get all your tables to play well together can be quite complex, but the flexibility of a relational table like this is invaluable to avoid going back and adding new fields all the time, then altering your programming without breaking something.
