Jim Davis wrote: > > Right now there is a proposal on the table to truly genericize the > consolidated table. Instead of (say) columns like âPolicyNumberâ, > âArchiveIDâ and âSSNâ the new table might have âKey1â, > âKey2â, âKey3â, etc. Each of these columns would be open, > essentially, to any data type and what appeared in them would depend on the > source archive of the record.
This is generally known as an Entity-Attribute-Value model: http://en.wikipedia.org/wiki/Entity-Attribute-Value_model > Current structure: > > ID Source FName LName PolicyNumber AccountCode SSN > ------------------------------------------------------------------ > 1 A Jim Davis 11111111 null null > 2 B null null 222222222 231234-d 123456789 > > Would become: > > ID Source Attribute Value > ---------------------------------------------- > 1 A FName Jim > 1 A LName Davis > 1 A PolicyNumber 11111111 > 2 B PolicyNumber 222222222 > 2 B AccountCode 231234-d > 2 B SSN 123456789 > > Of course this would âstretchâ the tables (our 5-6 million rows might > become 40 million) but it would allow for complete control when adding new > search criteria or fields. (Of course the system would probably be > normalized with a separate âAttributesâ table with the main table joined > to it). I'm not sure how performance would suffer (obivously every column > but "value" would be indexed). You are not indexing value? Not even a composite index on both attribute and value? Then how is "WHERE Attribute = 'LName' AND Value = 'Davis'" going to perform? > Any thoughts? Comments? Resources youâd like to share? Have I even made > myself clear? Moving from your current model to an EAV is so simple that my recommendation would be to just try it. It shouldn't take more then half an hour + processing time. When you have done that, try to run some of your current searches against the new model to see if you can get the data that you need in the time that you need it. Some things to include in your tests are queries you used to express as: - where A = X and B <> Y - where A = X and B NOT IN (Y, Z) - queries with typecasting - order by X (where X was numeric but is now stored as a string) - where A = B with both floats Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275919 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
