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 |
|
123sqlteam
Starting Member
4 Posts |
 Posted - 2008-03-03 : 14:09:48
|
| We have an entity such as a documentSearchKey that contains attribtes about a particular document. This document can have 1-N number of search keys or attributes. The classic Employee Table is a good example for a horizontal listing of attributes (fname, lname, SS#, address, etc.) because the employee entity has a "fixed" number of attributes so we can add columns across. For the documentSearchKey entity attributes can be considered search keys or where clause values.The documentSearchKey entity has variable number of attributes (docType A has 5 keys, docType B has 15 keys, etc) For this example each docType lives inside its on table so there is not a problem with mixing a variable number attributes inside the same table i.e. we will assume this table has 20 keys vertical or 20 columns horizontal as defined below.The problem is whether or not to add 20 columns across or to add 3 columns and create a non-normalized DB so additional keys can be added at will. The proposed table now contains 3 columns (docID, KeyID, KeyValue). Of course, 10 keys for 1 million records create 10 million rows Versus the traditional table with 1 million records always has 1 million records(keys are cols) where some columns contain blanks or nulls. Which design is better in terms of searching and performance? Also, books and links are welcome as well. This is a specific question to a production issue.Thank You Very Much ! |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-03-03 : 14:35:20
|
the proposed idea of (docID, KeyID, KeyValue) is called the EAV model and it has bad, terrible, horrible performance. it is flexible in that you may add new attributes without changing the table schema, but perf is very bad. it has plenty of other drawbacks as well. don't use it.give this a read: http://weblogs.sqlteam.com/davidm/articles/12117.aspx
elsasoft.org |
 |
|
|
123sqlteam
Starting Member
4 Posts |
|
|
|
|
|
|
|
Variable Primary Key Attributes