|
|
Posted by Erland Sommarskog on 09/21/05 00:35
(mittal.pradeep@gmail.com) writes:
> What is the better table design for a data collection application.
> 1. Vertical model (pk, attributeName, AttributeValue)
> 2. Custom columns (pk, custom1, custom2, custom3...custom50)
>
> Since the data elements collected may change year over year, which
> model better takes of this column dynamicness
The vertical model is certainly cleaner from a relational perspective.
It also requires less maintenance.
But admittedly queries can be more complex. If attributes can be of
different data types, you need some triggers to check this. A tip
is that the sql_variant data type is good in this case.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|