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 |
|
key
Starting Member
2 Posts |
 Posted - 2008-01-15 : 01:37:36
|
| Hi all,I have to come up with a design choice for a DB Table for a financial firm(i.e. Tables with Fixed column or Value-Pair). Case: SQL Server 2005 is used as a back-end for a web application(which basically is designed for doing web based analysis). The business people are insisting on a feature that enable power users to add fields to the underlying tables. One option proposed is named value-pair design approach? But from performance point of view, this seems a nightmare. How can this be integrated into static-column table approach. FYI, security policy of the client do not allow the underlying table structures to be changed by the user and currentlythere are 5000 fields, with a possibility to grow.Thanks so much for your suggestions!!Dilla |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
newsqlguy
Starting Member
13 Posts |
Posted - 2008-01-25 : 12:29:37
|
| this issue is as old as the days of databases.Try to check if keeping the custom fields as an XML extended data inside the record and then use Xquery to parse it out. sql2005 can create index on xml data.you could also use PIVOT on the attribute value table as following:SELECT [EntityId], [1] as [Age], [2] as [Name] FROM (SELECT EntityId, AttributeId, AttributeValue FROM [EntityAttribute]) eaPIVOT(MIN([AttributeValue])FOR [AttributeId] IN ([1],[2]))as pvtThis assumes that each entityid (e.g. customer) have only one value for each attribute.What you need is to dynamically generate teh column list and the IN list from a MetaData table CustomerAttributeMetaData. I never had a chance to check its performancealso, for key-value pair you could do certain optimizations to boost performance. yet it wont be as fast as static table structure.Didn't want to go to deep into details, but let me know if you need more help. |
 |
|
|
rprice
Starting Member
3 Posts |
Posted - 2008-01-29 : 05:33:33
|
| We have used a couple of approaches. Firstly we create 10 varchar(max) columns and let the users set the labels for these text fields. No formatting, no valuation, no indexing, just 10 spare columns for end uses to do what they want with.Second design we use is a attribute type table with (objectid INT, fieldid INT and value VARCHAR(MAX)). You setup types of fields and validation logic by fieldid and build dynamic field update sections for you front end. The exact structure is a lot more complex than that to ensure it performs well with high volumns. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-01-30 : 01:57:00
|
| Whatever suggestion you go for , consider the potential complexity of your SELECT statementsJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-01-30 : 18:38:31
|
| I've used a similar method to rprice but have 10 spare fields of each type and let the front end sort out how they are used by using meta data also stored in the DB. I have also used the EAV but restricted it to supplemental, non-searchable data. I have found this is an acceptable compromise. |
|
|
|
|
|
|
|
|
Database/Table Design-URGENT HELP!!!