Database Tables Designing help

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database Tables Designing help

Author

Topic

bakshi.ishan
Starting Member

3 Posts

Posted - 2011-03-10 : 01:03:37

Hi,
Please go through this, It is a long explanation but has been simplified.

I have 3 tables in my database namely : tbl_Product, tbl_CategoryMaster,
tbl_SubCategoryMaster
Then i have helping tables tbl_ProductCatMapping and tbl_productCatAndSubCatMapping

tbl_ProductCatmapping is used to save product id and its categoryids
tbl_productCatAndSubCatmapping is used to save the (product_id + category_id) and the Sub Categoryid and the amount field for that sub category of that product.

E.g.
Product = Calendar

Feature = Size
Sub Feature = 18' x 11'
Sub Feature = 18' x 13'
Sub Feature = 18' x 15'

Feature = Paper
Sub Feature = Gloss paper
Sub Feature = Gloss Cover

Feature = Printing
Sub Feature = One side Printing
Sub Feature = Both Side printing

Now what we provided to the client was he can save the amount for each sub feature seperately, so when the user orders a calendar whose size is 18' x 11' and paper is Gloss paper and Printing is One side then the amount of these sub features gets totalled and added to the base price of the product.

What client wants is to save the price as a whole after making a combination of sub features. For e.g.

He chooses calendar
Then he chooses Size = 18'x11'
Then he chooses Paper = Gloss Paper
Then he chooses Printing = One Side
And then puts a total price of this combination e.g. 100$

Then he chooses new Combination for the Same Product Calendar
Then he chooses Size = 18'x11'
Then he chooses Paper = Gloss Paper
Then he chooses Printing = BOTH SIDE
And then puts a total price of this combination e.g. 120$
and so on.

The problem is how to save the price of each combination as a whole seperately.

for reference view site : https://plifeprint.com/index.php?route=product/product&product_id=60
Explanation : http://screencast.com/t/EkLcsK5g

Thanks for your help.

Ishan Bakshi

bakshi.ishan
Starting Member

3 Posts

Posted - 2011-03-10 : 07:37:13

Cant anyone help me with this??

Ishan Bakshi

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-10 : 07:51:04

Hmm...I've had a (somewhat) similar problem before and the way I solved it was to create a table called "sales_offers" or something that has an OfferID and a TotalPrice (and probably some other columns as well). After saving a new "sales_offer" record with the total price, you fetch the OfferID and then save that together with all the selections the customers made to a separate table.

It's over 6 years since I did this and I remember that I wasn't very happy about the design, but it was the only way I could compare and save offers even if the user left the web-pages etc.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

bakshi.ishan
Starting Member

3 Posts

Posted - 2011-03-11 : 06:28:19

I have reformatted the my problem. Now is should be more clear to you. My problem is basically saving multiple combinations and each combination is going to have its own price. The solution that i made was to have a group id against each combination

so my combination table was going to look :
product_id subfeature_id Group_id Price
11 101 (18'x11') 1 150$
11 102 (Gloss Paper) 1 150$
11 103 (One Side Printing) 1 150$
Next Combination
11 101 (18'x11') 1 180$
11 102 (Gloss Paper) 1 180$
11 104 (One Side Printing- only this being changed) 1

180$

If the user selected the combination that does not exist then the value returned will be NULL or which would be again a prob for the admin.

Ishan Bakshi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources