Dov, thanks for this. Any readings on approach 4? George
On 6/29/06, Katz, Dov B (IT) <[EMAIL PROTECTED]> wrote: > > There are several approaches to solving this type of problem, imho, and > each one has costs and benefits, and I've given each of them a "report > card" (A being best, F being worst): > > 1) Xml into a field (as originally speculated) > Benefits: flexible design, structured data once retreived > Costs: useless for searching, data not typed, (all strings), storage > bloat due to markup > > DB Normal Design Grade: D > Performance Grade: C > Dev Ease Grade: A- > > > > 2). Create a properties table with 3 fields, ID , key, val and put > clustered index on ID and Key, and unclustered index on key > Benefits: still flexible, searchable by field > Costs: a bit more db complexity, but with proper indexing costs can be > minimized. Still all data are strings > > DB Normal Design Grade: B+ > Performance Grade: A- > Dev Ease Grade: B > > > > 3) Similar to 1 but use java.util.Properties for flat key-value pairs > Benefits: somewhat easy to search "key=value" substrings can be searched > for, cheaper than xml if all you want is key val pairs (xml more > flexible in terms of design of data) > Costs: most of the same costs as xml > > DB Normal Design Grade: D > Performance Grade: B- > Dev Ease Grade: B+ > > > > > 4). Improve #2 above with 3-4 properties table, one for date vals, one > for numeric , one for strings etc > Benefit: well designed, normalized, type safe (good for aggregate fcns > like sum, avg etc). Performance shouldn't be impacted with smart > indexing > Costs: more complicated, dao objects would need to run a union query > (easiest) or know which tables have which properties (harder, but > better, you can let users define their own types which helps you with > the ui, and then you'd know what keys belong in what tables) > > DB Normal Design Grade: A- > Performance Grade: B- > Dev Ease Grade: C+ > > > > 5) Actually let users design tables and columns per object type > Benefit: Perfectly normalized DB design, no joins needed to assemble > metadata on an object, you can do all aggregate queries, etc, Type Safe > Cost: Will complicate the DB, users choices can impact db performance > if not careful, Difficult to develop > > DB Normal Design Grade: A+ > Performance Grade: A > Dev Ease Grade: D > > > I've done variations of the above and its definitely one of those > TMTOWTDI kind of things > > Hth > Dov > > -----Original Message----- > From: Nick de Voil [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 29, 2006 6:50 AM > To: CF-Talk > Subject: Re: XML storage of metadata in database fields > > > We have an application that allows users to specify (at initial setup > > time) to specify what kind of metadata they want to enter when they > > work with digital media within the application. For example, one user > > may want to upload a bunch of Word doc files and then enter a set of > > metadata (say Title, Description, Source, Location, etc.). Another > > user may want to enter a combination of images, docs and movie files > > and then enter a different set of metadata (say Title, Description, > > Period, Style, Location, Dates, Sequence, etc.) If we go the > > traditional database route, this would involve lots of dynamically > > named fields which I envision as really messy. We think that storing > > these individual metadata sets as XML data (keyed to ID fields in the > > db) would be the way to go. Does anybody have any experience/thoughts > on this approach? > > I think the key question is, what are you going to use the metadata for? > > If you are going to want to use metadata fields for > searching/sorting/analysis, I would go with the traditional database > route. This is what we do in our CMS. A little complicated maybe - you > need a lot of tables to get a really flexible solution - but not messy. > > If the metadata of each set of objects is of interest only as a sort of > guide to that set of objects in isolation once retrieved from the > repository, and particularly since you mention Sequence, then your idea > is a bit reminiscent of a "manifest" file that you attach to objects in > an object packaging scheme like IMS Content Packaging, which is a > desperately complicated e-learning interoperability standard but you > might be interested in some of the concepts: > > http://www.imsproject.org/content/packaging/cpinfo10.html > http://www.imsproject.org/metadata/mdbest01.html > > Nick > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245058 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
