Mapping different XML documents to a single database schema?

[Lasse Koskela]

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.

<?xml version="1.0"?>
<request version="1">
  <securitycode>12345678901234567890</securitycode>
  <country>FI</country>
  <model>ABC-1</model>
  <sms>
    <msisdn>[phone]</msisdn>
    <message>R FI 12345678901234567890</message>
  </sms>
</request>
 
<?xml version="1.0"?>
<request version="1">
  <securitycode>12345678901234567890</securitycode>
  <country>FI</country>
  <model>ABC-1</model>
  <ivr>
    <transactionid>AFaETWvba4ag5aWVT4e65v4AV</transactionid>
  </ivr>
</request>

[Ilja Preuss]

What do you log them for? How do you need to retrieve them?
Could you simply write the XML into a CLOB/BLOB?

[Lasse Koskela]

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]

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:

Table Name: REQUEST
--------------------------------------------
REQUEST_ID    | NOT NULL | NUMBER      | PK
VERSION       |          | NUMBER
SECURITY_CODE |          | NUMBER
COUNTRY       |          | VARCHAR(2)
MODEL         |          | VARCHAR(20)
 
Table Name: SMS
--------------------------------------------
SMS_ID        | NOT NULL | NUMBER      | PK
REQUEST_ID    | NOT NULL | NUMBER      | FK
MSISDN        |          | VARCHAR(20)
MESSAGE       |          | VARCHAR(100)
 
Table Name: IVR
--------------------------------------------
IVR_ID        | NOT NULL | NUMBER      | PK
REQUEST_ID    | NOT NULL | NUMBER      | FK
TRANSACTION_ID|          | VARCHAR(30)

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

REQUEST
---------------------------------------------
101, 12345678901234567890, 'FI', 'ABC-1'
102, 12345678901234567890, 'FI', 'ABC-1'
 
SMS
---------------------------------------------
201, 101, '[phone]', 'R FI 12345678901234567890'
 
IVR
---------------------------------------------
301, 102, 'AFaETWvba4ag5aWVT4e65v4AV'

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 ]