SOT: XML storage of metadata in database fields
All, I tried searching for information on this, but I am probably not searching for the right jargon. 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? It would be really nice to read any articles or whitepapers on this too. Thanks, George ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245053 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
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:245054 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
Re: SOT: XML storage of metadata in database fields
On Thursday 29 June 2006 11:09, George Abraham wrote: We have an application that allows users to specify (at initial setup time) Why not just add the users choice as columns ? -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. We are pleased to announce that Halliwells LLP has been voted AIM Lawyer of the Year at the 2005 Growth Company Awards ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245055 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: XML storage of metadata in database fields
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
Re: XML storage of metadata in database fields
On 6/29/06, Nick de Voil [EMAIL PROTECTED] wrote: 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. Yes we will be using these metadata fields, maybe not as much for sorting, but definitely for searching. We do use a different searching mechanism that is not tied to the db at all, except to the primary key. 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 Actually Nick, one of the requirements of this project (at least down the line) is to be able to export the set of objects as a learning object using IMS. So yes, I am a bit familiar with that. George ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245057 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
Re: XML storage of metadata in database fields
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
Re: SOT: XML storage of metadata in database fields
On 6/29/06, Tom Chiverton [EMAIL PROTECTED] wrote: On Thursday 29 June 2006 11:09, George Abraham wrote: We have an application that allows users to specify (at initial setup time) Why not just add the users choice as columns ? Tom, If each user was allowed to specify their metadata fields, wouldn't we land up with a huge number of fields, most of which would be empty? Or am I understanding you wrong? Thanks, George ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245060 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SOT: XML storage of metadata in database fields
See my breakdown. It puts many of the options into perspective. You will end up with a huge number of fields, but you might want to design this as somewhat of a OO design, with base object which you define, and ObjectExtension tables, which users can create, but then others can reuse. Then you'd join the tlbObjects with tblExtImages or tblExtMediaFiles etc... Which would be strongly typed and well designed... -Original Message- From: George Abraham [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 7:51 AM To: CF-Talk Subject: Re: SOT: XML storage of metadata in database fields On 6/29/06, Tom Chiverton [EMAIL PROTECTED] wrote: On Thursday 29 June 2006 11:09, George Abraham wrote: We have an application that allows users to specify (at initial setup time) Why not just add the users choice as columns ? Tom, If each user was allowed to specify their metadata fields, wouldn't we land up with a huge number of fields, most of which would be empty? Or am I understanding you wrong? Thanks, George ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245061 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SOT: XML storage of metadata in database fields
On Thursday 29 June 2006 12:50, George Abraham wrote: If each user was allowed to specify their metadata fields, wouldn't we land up with a huge number of fields, most of which would be empty? Or am I understanding you wrong? Ahh, I thought you were talking about customising it for a group of users/company. -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. We are pleased to announce that Halliwells LLP has been voted AIM Lawyer of the Year at the 2005 Growth Company Awards ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245063 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: XML storage of metadata in database fields
On Thursday 29 June 2006 12:15, Katz, Dov B (IT) wrote: TMTOWTDI kind of things Que ? -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. We are pleased to announce that Halliwells LLP has been voted AIM Lawyer of the Year at the 2005 Growth Company Awards ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245064 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: XML storage of metadata in database fields
Katz, Dov B \(IT\) 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 Unless your database supports functional indexes. data not typed, (all strings) Unless your database supports schema validation. storage bloat due to markup Not that bad if the database supports inline compression. Columns have overhead too. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245066 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
RE: XML storage of metadata in database fields
BTW SQL Server now supports XML natively, so if you store XML in the database, you can parse it a lot easier. Russ -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 13:39 To: CF-Talk Subject: Re: XML storage of metadata in database fields Katz, Dov B \(IT\) 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 Unless your database supports functional indexes. data not typed, (all strings) Unless your database supports schema validation. storage bloat due to markup Not that bad if the database supports inline compression. Columns have overhead too. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245071 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: XML storage of metadata in database fields
We do use SQL Server 2000 currently and we had heard that SQL Server 2005 was much better for working with XML. That did factor into our curent thoughts. George On 6/29/06, Snake [EMAIL PROTECTED] wrote: BTW SQL Server now supports XML natively, so if you store XML in the database, you can parse it a lot easier. Russ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245073 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: XML storage of metadata in database fields
If you use IIS, you may want to look into the IIS SQL integration. Using this you can provide direct HTTP XML services into your database. russ -Original Message- From: George Abraham [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 15:16 To: CF-Talk Subject: Re: XML storage of metadata in database fields We do use SQL Server 2000 currently and we had heard that SQL Server 2005 was much better for working with XML. That did factor into our curent thoughts. George On 6/29/06, Snake [EMAIL PROTECTED] wrote: BTW SQL Server now supports XML natively, so if you store XML in the database, you can parse it a lot easier. Russ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245074 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: XML storage of metadata in database fields
Has done since 2000. -Original Message- From: Snake [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 14:39 To: CF-Talk Subject: RE: XML storage of metadata in database fields BTW SQL Server now supports XML natively, so if you store XML in the database, you can parse it a lot easier. Russ -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 13:39 To: CF-Talk Subject: Re: XML storage of metadata in database fields Katz, Dov B \(IT\) 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 Unless your database supports functional indexes. data not typed, (all strings) Unless your database supports schema validation. storage bloat due to markup Not that bad if the database supports inline compression. Columns have overhead too. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245076 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: XML storage of metadata in database fields
Yes, but I believe SQL Server 2005 better supports XML, natively storing it as an xml type, and letting you do queries and updates on xml files as if they were tables. Russ -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:Neil.Robertson- [EMAIL PROTECTED] Sent: Thursday, June 29, 2006 10:50 AM To: CF-Talk Subject: RE: XML storage of metadata in database fields Has done since 2000. -Original Message- From: Snake [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 14:39 To: CF-Talk Subject: RE: XML storage of metadata in database fields BTW SQL Server now supports XML natively, so if you store XML in the database, you can parse it a lot easier. Russ -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 13:39 To: CF-Talk Subject: Re: XML storage of metadata in database fields Katz, Dov B \(IT\) 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 Unless your database supports functional indexes. data not typed, (all strings) Unless your database supports schema validation. storage bloat due to markup Not that bad if the database supports inline compression. Columns have overhead too. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245077 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=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: XML storage of metadata in database fields
Indeedit has XQuery. But 2000 supported XML data but ont to the same degree as 2005. -Original Message- From: Russ [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 16:29 To: CF-Talk Subject: RE: XML storage of metadata in database fields Yes, but I believe SQL Server 2005 better supports XML, natively storing it as an xml type, and letting you do queries and updates on xml files as if they were tables. Russ -Original Message- From: Robertson-Ravo, Neil (RX) [mailto:Neil.Robertson- [EMAIL PROTECTED] Sent: Thursday, June 29, 2006 10:50 AM To: CF-Talk Subject: RE: XML storage of metadata in database fields Has done since 2000. -Original Message- From: Snake [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 14:39 To: CF-Talk Subject: RE: XML storage of metadata in database fields BTW SQL Server now supports XML natively, so if you store XML in the database, you can parse it a lot easier. Russ -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 13:39 To: CF-Talk Subject: Re: XML storage of metadata in database fields Katz, Dov B \(IT\) 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 Unless your database supports functional indexes. data not typed, (all strings) Unless your database supports schema validation. storage bloat due to markup Not that bad if the database supports inline compression. Columns have overhead too. Jochem ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245079 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
RE: XML storage of metadata in database fields
If you use IIS, you may want to look into the IIS SQL integration. Using this you can provide direct HTTP XML services into your database. I would strongly recommend against this, unless you're in a very strictly controlled environment, for security reasons. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245082 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
RE: XML storage of metadata in database fields
It is very very easy to make this secure. You certainly do not give open access to the database. You create XML templates that are like views, they allow limited access to the data, and can be restricted to select/update/whatever. The reason Dave prob said that is because you can allow the database to be queryied by simply sending an SQL statement on a URL or form post to the XML interface. But you do have to set it up to allow this in the first place. Of course with windows you can password protect resources too. It is only as insecure as you make it, just as your CFML apps will be insecure you do not password protect them and protect from SQL injection. Russ -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 17:05 To: CF-Talk Subject: RE: XML storage of metadata in database fields If you use IIS, you may want to look into the IIS SQL integration. Using this you can provide direct HTTP XML services into your database. I would strongly recommend against this, unless you're in a very strictly controlled environment, for security reasons. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:245087 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
