Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
higgsy
Starting Member
3 Posts |
 Posted - 2009-10-28 : 14:10:20
|
| Hello,I'm designing a database that only has a few core products, however each product has a number of options that will affect the price. In my example, the product is acrylic prints.The products are priced based on their size, on top of that there are options such as the quality of the finish i.e. opaque or transparent in this example, that also affect the price.Just to complicate things further, they do have products that follow a standard structure i.e. they have a static title, description and price.Can anyone give me any tips on the best way to design the structure for the products? Thanks in advancehiggsy |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-28 : 14:31:32
|
As an approach:design your product table(s)design a table for additional services and its prices.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-28 : 16:39:36
|
| I'd consider a few tables here. Try to see around the corner.table 1 products with product id, description for example, table.table 2 product_options option_id, description for example opaque finish.table 3 product_option_prices product_id,option_id, price, from_date, to_date (or an active flag worst case)table 4 product_prices product_id, price, from_date,to_date (or an active flag worst case)My thinking on breaking it out this way is you want to consider when someone asks you for a report for the year. How much of product x did we sell and at what price. If you have just 1 column for price and the price changed during the year, say from $10 to $20 what are you going to report? You didn't make $20 on each product over the course of the year, you made $10 for part of the year and $20 for another part of the year. This way you're insulated from price changes and the same concept with the options table. If you really think you're going to blow out and have millions of combinations then I suppose you could just have an "archive" table and store your price changes there. That approach however means someone has to remember to move things before a price update so make sure there's warnings and documentation all over that it needs to be done. Just my 2 cents. Try to see around the corners and how things are going to work if your data changes a little bit before you create the schema. A day or two of thought now will save you hours coding around a schema design you can't change later on.Mike"oh, that monkey is going to pay" |
|
|
|
higgsy
Starting Member
3 Posts |
Posted - 2009-10-29 : 07:47:48
|
| Hi mfemenel,Thanks for your response. I can see that approach working, are you suggesting that sizes become an option in the product_options table? How would this approach lend itself to products that have a more traditional structure, i.e. they have a set price?Thanks againhiggsy |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-29 : 09:05:51
|
| let's take a simple example such as a shirt. product detail columns might be Size/Color/Price. Now contrast that with say a table or something. Size may or may not be null, color might still be used for something like finish and price is still relavent. You know your business better than I do I'm sure so these are things you're going to have to kick around. Think about it for a bit and if you're still not sure post a specific example. A word of caution though prices are never "set". What if the economy tanks..no wait..already happened.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
|
|
Product database design with product options