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 |
|
Desmag
Starting Member
18 Posts |
 Posted - 2005-11-12 : 15:20:10
|
| Hi guys,I'm building a products database (I'm at ERD stage) and I'm stuck on one thing.The problem is that products stored in my DB can have many optional parameters. I also want my users to be able to add new parameters. Say there are 20 parameters describing a hoover (Scottish for vacuum-cleaner ;-) ) and my user wants to add a new one.It wouldn't be a problem normally, its just parameters can have different types of fields they are stored at (otherwise I would just use many to many relation between Products and Products_parameters tables).So far I've identified 3 (its a minimum I think) types of values I would like to use (Text, Decimal, Varchar). I thought I would put them in 3 different tables with many to many reference with Products table. Question now is: is this a right approach? Something tells me that it isn't... Is there a different way of doing multiple parameters for products?Thanks in front, for all your help and suggestions. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-11-12 : 15:47:32
|
Might want to look at this, and the article it references:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57307Used in moderation I think the entity-attribute-value structure is very handy for scenarios like yours, the key word is MODERATION.  As the response indicates, it doesn't work well for entire databases (I once looked at a trouble ticket system that was full EAV on every table, very sad, impossible to work with) |
 |
|
|
Desmag
Starting Member
18 Posts |
Posted - 2005-11-12 : 16:17:58
|
| I've read it - it's a very clever stuff. Thank you robvolk.So really, one table for each datatype is the right approach. Oh dear...PS. Expect increased number of SQL questions ;) |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-12 : 18:25:31
|
| >>So far I've identified 3 (its a minimum I think) types of values I would like to use (Text, >>Decimal, Varchar). I thought I would put them in 3 different tables with many to many reference >>with Products table.A HUGE round of applause for this man/women! You have no idea (or maybe you do) how many so called data modellers don't even get this far..I personaly think EVA's are evil but when you are thrown into an unwanted battle.. FIGHT like hell!DavidMA front-end is something that tries to violate a back-end. |
|
|
|
Desmag
Starting Member
18 Posts |
Posted - 2005-11-12 : 22:08:06
|
| Hey MR byrmol... :)I'm working on my EVA'ls for good few hours now. They seem to be interesting, although I'm looking forward for data input attempts. Just can't picture the whole thing at all. I'll worry later though.RE your observation about fields datatypes - thank you for applause, although those fields weren't a matter to me at all. |
|
|
|
|
|
|
|
|
Products DB design