Design Database – Best Practice
I need to implement an SQL server db, that will contain information about products as follow:
There will be the following properties:
**ProductName ProductTitle ProductPrice CreatedDate CreatedBy**
(now it get’s complicated)
**Property1 (MIN value, MAX value) Property2 (MIN value, MAX value) Property3 (MIN value, MAX value) Property4 (MIN value, MAX value) Property5 (MIN value, MAX value) Property6 (MIN value, MAX value) ........... Property40 (MIN value, MAX value)**
All products will have the same structure, how can I implement the properties that will contain the values min and max? So, each product will have a min and max value for each of the Property from 1 – 40. The value in the min and max is decimal.
Should I create one Products table where I have the basic details (name, title, price, date ..) and than for each property create one table Property1 for example with foreign key to Products table?
Is there a better way? What do you recommend ?
2 Solutions collect form web for “Design Database – Best Practice”
I would have a PropertyMinMax table
ProductID int PropertyID int MinValue decimal(x,y), MaxValue decimal(x,y)
and potentially, a Property table containing description information for each type of property.
It all depends on what you need to do with this data.
The neatest way is to abstract those properties away as @podiluska suggests – but that makes querying the data rather complex; imagine building a query to find products where property1 min value greater than 2, and property 2 value max smaller than 12 and property 3 min between 16 and 99…
The simplest way is ugly, but makes the querying far more efficient – you have a very wide table, with each property min and max as separate columns.