Product Feature Pricing - SQL Server Forums

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

Database Design and Application Architecture

Product Feature Pricing

Author

Topic

dohamsg
Starting Member

22 Posts

Posted - 2012-06-02 : 03:58:04

Hi,

'ProductA' can be of Quality1 or Quality2
'ProductA, Quality1' can be of Color1 or Color2
'ProductA, Quality2' can be of Color1 or Color2
'ProductA, Quality1, Color1' has Price1
'ProductA, Quality1, Color2' has Price2
'ProductA, Quality2, Color1' has Price3
'ProductA, Quality2, Color2' has Price4

'ProductB' can be in Color1 or Color2, no Quality involved
'ProductC' can be in Quality1 or Quality2, no Color involved

Question: How to model this in table schema?

If a user selects for example : 'ProductA, Quality1, Color1', how to query to get the right price?

The user can also query for : 'ProductB, Color1' to get its price.

sumurtinggi3plg, do you have the SQL script of the book : len silverston data model resource book?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-03 : 01:22:12

i would model it as follows


a. Product table (ProductID,ProductName)

with values

1          ProductA
2          ProductB
3          ProductC
....


c.ProductAttribute table (AttributeValueID,ProductID (fk to product table),Quality,Color,Price)
with values as

1        1      Quality1     Color1    Price1
2        1      Quality1     Color2    Price2
3        1      Quality2     Color1    Price3
4        1      Quality2     Color2    Price4
5        2      NULL         Color1    Price5
...

with values

1 1 Price1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dohamsg
Starting Member

22 Posts

Posted - 2012-06-03 : 05:28:39

Thanks indeed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-03 : 15:20:06

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources