File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Mapping different XML documents to a single database schema? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Mapping different XML documents to a single database schema?" Watch "Mapping different XML documents to a single database schema?" New topic
Author

Mapping different XML documents to a single database schema?

Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
So, I have a request processing system which receives slightly different XML messages from different sources (client applications). The requirement is that every request needs to be logged into a database. Now, the question remains, how should I design the database schema for storing these log entries when one complex element in the incoming requests varies?
Of course I could have a table per different "variant" section, but I wouldn't want to go there because the number of these variants may grow over time causing maintenance headache.

Here are two example requests that should be possible to handle (the exact XML schema is not written in stone, though). Basically, the DTD for these requests would specify something like <ELEMENT request (securitycode, country, model, (sms | ivr))> where there's always either an "sms" or "ivr" element (and possibly something else in the future) and the contents of these alternatives are different.



Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
What do you log them for? How do you need to retrieve them?
Could you simply write the XML into a CLOB/BLOB?


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
What do you log them for? How do you need to retrieve them?
I don't know... I will after 3 hours when I have a chance to discuss with the customer. I'm basically doing research on the alternative approaches for each scenario.
Could you simply write the XML into a CLOB/BLOB?
Yes, I could. And that's what I'll probably end up doing unless the customer has something in mind for the future. Based on what we know right now, the simplest thing would be to add two columns into the existing table and simply leave the other column unused for half of the requests.
Jason Menard
Sheriff

Joined: Nov 09, 2000
Posts: 6450
IMHO, I would avoid the whole BLOB/CLOB thing if possible. You pretty much kill any searchability and flexability by doing this. Based on the examples you gave, I might think about something like this:

Given the above schema, your populated tables might look like this given your data:

So yeah, you have more than one table, but it's not a big deal if you need to add additional variants other than SMS or IVR in the future, since you can see how all these sections are joined to the main request. Just add a new table, with a foreign key field for the REQUEST_ID, for any new sections that come along.
[ December 01, 2003: Message edited by: Jason Menard ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Mapping different XML documents to a single database schema?
 
Similar Threads
Problems trying to send XML over HTTP POST
problem with String [] in axis 1.3 and .net client
How to get element names from schema
Using java.lang.Arraylist object in WSAD WebServices
Want to understand SOAP Exception's meaning?