i'm writing an app that will store some xml data into a db, and the java app will render the data from the db; either as html, or some other way, the point is that there will be various kinds of text markup. So some data like this:
should the db have tables like entry, para, heading etc and the app will use queries to piece it all together, or should I just have one "entry" table and have a text row, and directly store the xml, and have the java app break it up into different objects, or render it using xslt, or whatever?
What's the standard for text-based database apps like this?
You have a choice. You can either replicate the XML structure in the database, use an XML data type (if your database supports it) or treat it as a simple string.
If you replicate the XML structure you add a considerable level of complexity to your data management code and you tighly couple the XML structure to your data model (i.e. change an attribute in your document and you have to alter your database). However, you also use a database as it is intended to be used, you know the data it contains is valid and some databases provide tools to do just this.
If your database supports an XML data type it may also support validation of this type as a constraint, which is useful because you can be sure all XML documents you store are valid, but it tightly couples your design to one database implementation.
If you treat it as one big string things are much easier but you push the validation out to any client applications that use the data. XML can be self validating (with DTDs or a schema) so this is not a huge concern, it just gives you more than one place to worry about data integrity.
would you say it's a bad idea to have tables like ENTRY, DATE, PARA and to have a long query piecing the data together that way?
Joined: May 09, 2007
just thinking about this a little more...
I think replicating the xml structure is the best way to go for now because: 1) like you say, the db is being used as it's meant to be 2), it will allow the app to search for specific values; date, etc. 3) if I enter the entire thing as a string, there will be no differentiation between datatypes; int, string, date, etc, and that doesn't sound good to me. Also, I want to say that most db based text apps probably follow this model, and it's an opportunity to brush up on my SQL.
On the downside, simply putting a little paragraph together requires a lot of complex hoop jumping in terms of the queries...(complex to me, I'm a relative sql noob) and like you say, there it tight coupling to the xml structure.
If I used xml and treated it as a string, I would lose the ability to have different datatypes in the db, also, a tag name would come up as a match if the user entered that word as a search term..."entry" in the example above. So, you would have to do a general query from the db, have the client app break up the results into xml nodes and sort and present the results...that sounds like a pain, too.
Am I right in saying that the generally accepted way to do this is by having the structure defined in the db?