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 |
|
tsongrounds
Starting Member
2 Posts |
 Posted - 2009-12-21 : 01:57:38
|
| I have been contemplating for a year or so on a structure for my future tables that I create. Every year or so, if I went with standard pratices, I would have to add new columns to a table while some columns would be obsolete. I would like the table to be as dynamic as possible, but still be somewhat user friendly.I lean back and forth in regards to designing tables with columns or by rows as attributes. I know there would be more overhead in administrating a table designed with rows as attributes, however; this would ensure any particular grouping of records would be valid for that point in time rather than having multiple columns containing (null) values. The other thought I also had was if I really need to output a rows as attribute in a table format, I would generate user defined table to achieve this, however; I would still have the same (null) value issues if there are columns that are no longer used.Given the criteria above, which method would be best to implement? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-12-21 : 03:13:46
|
| As far as I can understand your evaluating the need for using the EAV model (http://en.wikipedia.org/wiki/Entity-attribute-value_model) which supports a changing data model, but at the cost of performance and complexity in application design. Most DBA's will probably argue against using this model because of it's increasing complexity over time, improper use of datatypes and te difficulties of writing efective queries. Search here or the web for "eav" and you'll find plenty of arguments...- Lumbagohttp://xkcd.com/327/ |
 |
|
|
tsongrounds
Starting Member
2 Posts |
Posted - 2009-12-21 : 03:25:56
|
| Most of the points made in WP were things I have already thought of. Unfortunately, I have not been able to find the key point to sway my decision from design aspect to another. Are there any other data modeling techniques that would be a compromise of the two? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-12-21 : 06:40:42
|
| If you want my advice I'd say you should stick to the traditional design techniques and keep columns as columns. Unless you have an extreme need for flexibility the EAV models cons far exceed the pros...- Lumbagohttp://xkcd.com/327/ |
|
|
|
|
|
|
Table Design - Columns vs Rows