|
|
Posted by mittal.pradeep on 09/22/05 03:02
Thanks a lot for the reply.
1. I am not able to understand why i require a 10 way join though. If i
have a mapping between custom column and actual column, all i need is a
dyanamic sql generated from the mapping.
E.g.
table
-----
pk, custom1, custom2, custom3...custom10
map
---
customColName ActualColName
custom1 ActualName1
custom2 ActualName2
....
Now I can generate dynamic sql using map.
2. As long as number of custom columns is enough to take care data
element additions which happen in a year. All that is needed is
addition of new elements to mapping table to decifer newly assigned
custom columns
Erland Sommarskog wrote:
> (mittal.pradeep@gmail.com) writes:
> > 1. But the data collection and reporting is in horizontal format. If
> > collected data is edited vertically, won't there be a extra steps of
> > converting horizontally obtained data to vertical and then vertical to
> > horizontal reports. In custom column model data always remains
> > horizontal. Won;t performance not be a issue in taking care of two
> > extra steps required in vertical model.
>
> If you are to present ten of those custom values as columns in a report,
> you get a 10-way self-join. Certainly bulky in code. Performance is
> probably not top-notch, but I don't see that it would be absymal.
>
> > 2. Won;t the concurrency be a issue, considering the fact that a
> > logical single horizontal row is edited as say 10 rows. Two people
> > might be changing same Primary key's different attributes at the same
> > time.
>
> Good point. This can be handled fairly easily, but it requires more
> careful programming than the horizontal method.
>
> Overall, there certainly is a tradeoff. If the set of custom fields are
> faily stable, only change once per year or so, you might be prepared to
> take the extra maintenance cost. But if users asks for new fields every
> week, then the horizontal method could be a nightmare.
>
>
> --
> 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
[Back to original message]
|