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.
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
Joined: Jan 23, 2002
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.
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 ]