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 |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2010-03-29 : 22:03:14
|
I need to design a datatable for a materials list for any type of table since the types are user defined. The users want to be able to define the materials by either a description or physical characteristics of the material:One possible solution is the give the user a set number (10) of character based columns which can be populated with values, for example:TypeID | RecId | P1 | p2 | p3 | P4 |..... | P10-------------------------------------------------------------------MESH | 1 | 4 | 4 | 4/4 | NULL | NULL | NULLFLATBAR | 2 | 4 | 1/4 |NULL | NULL | NULL | NULLAGG | 3 | SAND | NULL | NULL | NULL | NULL | NULL As you can see, this approach will contain alot of null, and the field names aren't really defined, although I guess they could be defined in the Types table. But I am not convinced this approach is correct.I know of two other possible approaches:1) XML Data type. Not really sure of the queryability, especially since I may use Entity Framework for the DAL.2) Entity Value Pair. I think this is the best approach, but the EAV structure seems to be looked at in a very negative light...Any suggestions to help me decide?ThanksMike |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2010-03-30 : 08:21:37
|
| Thanks for the reply tkizer. I was hoping to get more feedback. What are the pros/cons of one method over the other? The "catch-all":Pros:1) queryabilityCons:1) not very extensible.2) Type safety, since all columns would be character based.3) Limited number of attributes (10 columns).The XML:Pros:1) Strict enforcement of formatting using templates.Cons:2) Queryability3) Use of DOM document in code to parse XML (Slow?).EAVPros:1) Extensibility2) Limitless number of attributes.3) With good table structure, type safety.Cons:2) QueryabilityAny thoughts on this.Mike B |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Design suggestions?