> -----Original Message----- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED] > Sent: Friday, April 20, 2007 7:41 AM > To: CF-Talk > Subject: Re: [OT] Generic Database Design Opinions? > > 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
Great! I knew it had a name but I couldn't find it. No maybe I can do some usable research on it. ;^) > 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? That's true. My thinking (when I wrote that) was that since "Value" could (and would) be essentially any datatype an index wouldn't be all that useful - but yes, that's obviously wrong. > > 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. I hope to try... if I can get space in the MainFrame region (due to costs our development and integration regions don't have the capability to store the complete dataset - not even a significant chunk of it). I'm not a DBA or admin on these systems - NOTHING is simple when dealing with corporate infrastructure teams. ;^) Still, even if I could get a small comparison set - perhaps a few hundred thousand records. That would at least tell us whether it's worth it to pursue on a larger scale. As an aside, I'm not being asked to look at this as a database expert - because I'm not a database expert (not even close, especially at these scales). Rather as the new person on the team I'm being asked to provide an unbiased impression of the current system and offer ideas of where we might go. Just a "hey, you've been around systems like this for a long time, whatcha think?" kind of thing. Jim Davis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275924 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
