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:245056 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
