Hiya,

I have a design challenge for you! I'm a Java
programmer and have a pet project that I'm working
on. Part of it requires a relational DB back end to
store logging information. Now my (very!) limited DB
design experience has not come up with a solution to
the problem I'm facing...

The logs I'm processing are in XML format (not a
problem) and each log entry can come in 4 pieces:
1. Request header (compulsory)
2. Request extra data (optional)
3. Response header (compulsory)
4. Response extra data (optional)

Every request will always have parts 1 and 3, and
these parts will all have the same data elements.
Parts 2 and 4 are optional (though most entries will
have them) and each of the elements within these
data elements will be different depending on the
log entry.

For example a logon (very simple):
<request_header>
<request_name>logon</request_name>
<tstamp>2003-11-18 10:05:36.441</tstamp>
</request_header>
<request_extra_data/>
<response_header>
<result>0</result>
</response_header>
<response_extra_data/>

File transfer:
<request_header>
<request_name>file transfer</request_name>
<tstamp>2003-11-18 10:13:46.422</tstamp>
</request_header>
<request_extra_data>
<filename>data.txt</filename>
<from>/data/users</from>
<to>/data/milo</to>
</request_extra_data>
<response_header>
<result>0</result>
</response_header>
<response_extra_data>
<filesize>1688534</filesize>
</response_extra_data>

So, then each request type has a defined set of
extra elements (which can also change from time
to time). I haven't been able to figure out an efficient
table layout, but maybe you guys can! Please let me
know of any ideas you have and if you need any more
information...

Cheers,

Kev