I'm having a heck of a time trying to figure out the best table structure for a site I'm working on.
The site will be selling products that are highly customizable - we'll call them widgets. Each widget can have one or more colors, varying widths and lengths, and one or more accessories. Pricing for the widgets depends on these factors as well as the quantity ordered. So it's not as simple as a table with product - quantity - price.
What's the best way to store all of this information? Should each element be separated into its own table or should there just be a generic "options" table that can store all the different elements of each widget? Or is there a better way?
Thanks!
SQL structure help for customized products
Moderator: General Moderators
-
WidgetMann
- Forum Newbie
- Posts: 2
- Joined: Tue Jul 28, 2009 5:48 pm
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: SQL structure help for customized products
From your description you'll want something like this:
PRODUCTS: product_id, name, width, length
COLOURS: colour_id, name
ACCESSORIES: accessory_id, name
PRODUCTS_COLOURS: product_id, colour_id
PRODUCTS_ACCESSORIES: product_id, accessory_id
That's with the assumptions that one or more product can have the same colour or accessory associated with it. If each product has unique colours and accessories you won't need the many-to-many cross-reference tables. Pricing could be implemented into this depending on what you mean exactly. For example, each product, colour and accessory could have a set price, or each product and it's associated colour and accessory could have a price. Quantity doesn't really matter, that's just a case of multiplication.
This is not taking into consideration how much load you're expecting on this database. A less normalised structure would be appropriate if you're expecting heavy load, because storage space is much less expensive than CPU cycles.
PRODUCTS: product_id, name, width, length
COLOURS: colour_id, name
ACCESSORIES: accessory_id, name
PRODUCTS_COLOURS: product_id, colour_id
PRODUCTS_ACCESSORIES: product_id, accessory_id
That's with the assumptions that one or more product can have the same colour or accessory associated with it. If each product has unique colours and accessories you won't need the many-to-many cross-reference tables. Pricing could be implemented into this depending on what you mean exactly. For example, each product, colour and accessory could have a set price, or each product and it's associated colour and accessory could have a price. Quantity doesn't really matter, that's just a case of multiplication.
This is not taking into consideration how much load you're expecting on this database. A less normalised structure would be appropriate if you're expecting heavy load, because storage space is much less expensive than CPU cycles.
-
WidgetMann
- Forum Newbie
- Posts: 2
- Joined: Tue Jul 28, 2009 5:48 pm
Re: SQL structure help for customized products
Thanks for the quick reply. That's more or less what I was thinking.
However, quantity does matter as the pricing depends on quantity. For example, if a customer orders 100 widgets, the price per-widget is $5.00 each. But if the customer orders 500 of the same widgets, the price drops to $3.00 each. How would I implement this pricing structure?
I don't expect heavy database load so I'm not worried about that.
However, quantity does matter as the pricing depends on quantity. For example, if a customer orders 100 widgets, the price per-widget is $5.00 each. But if the customer orders 500 of the same widgets, the price drops to $3.00 each. How would I implement this pricing structure?
I don't expect heavy database load so I'm not worried about that.
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: SQL structure help for customized products
Are the quantity totals done by percentage? Or set margins?
It entirely depends on how you intend to calculate it. From what I assume I think an extra table like:
QUANTITY_PRICES: quantity_price_id, product_id, price_per_product, quantity_min
Where quantity_max would hold the lowest quantity available to order at that price. So by default every product_id would have a quantity_min = 0 associated with it.
If the prices depend on the accessories and colours then you'd have to do it a different way, maybe:
QUANTITY_PRICES: quantity_price_id, product_id, price_reduction, quantity_min
Where price_reduction is the amount to take away from the total depending on quantity ordered.
Side note: quantity_price_id is included for ease of reference but isn't really needed because product_id/quantity_min could be used as a primary key.
It entirely depends on how you intend to calculate it. From what I assume I think an extra table like:
QUANTITY_PRICES: quantity_price_id, product_id, price_per_product, quantity_min
Where quantity_max would hold the lowest quantity available to order at that price. So by default every product_id would have a quantity_min = 0 associated with it.
If the prices depend on the accessories and colours then you'd have to do it a different way, maybe:
QUANTITY_PRICES: quantity_price_id, product_id, price_reduction, quantity_min
Where price_reduction is the amount to take away from the total depending on quantity ordered.
Side note: quantity_price_id is included for ease of reference but isn't really needed because product_id/quantity_min could be used as a primary key.