Newbie database design

I need to store "Message Traces" in a RDMS. A "Message Trace" is a list of "Attributes" describing a "Message". Some "Attributes" can be optional.

I have mutltiple designs I can think of in order to do that :

1. "Flat" Design : Creating a table like : MessageTrace (Attr_1, Attr_2, Attr_3, ..., Attr_n) and using it as such. Empty Attributes will be NULL. pros :
   • Ease of use
   • Possibility of SELECTs on any "Attribute" cons :
   • NULL Fields may cost memory space, and are hated by some
   • What if we need one day extra "Attributes" like Attr_(n+1), Attr_(n+2), etc (though I know the ALTER TABLE and so on...) ?
2. "Opaque" Design : Creating a table like : MessageTrace (Attr_1, Attr_2, Attr_3, OptionalAttributesInXML : CLOB) Attr_1, Attr_2, Attr_3 can't be NULL, and are used as keys and INDEXES. OptionalAttributesInXML is a CLOB Field formatted for example in XML and that indicates which optional "Attributes" are present, and which are not. pros :
   • Ease of adding new "Attributes" : costs only to redesign the XML Format.
   • "No" space is lost cons :
   • Need to format the "Attributes" in XML
   • Can only SELECT on a few "Attributes"
   • XML Format is quite memory costly also...
3. "Supertype" Design : Creating tables like MessageTrace(MessageTraceID, Attr_1, Attr_2, Attr_3) OptionalAttr_1(MessageTraceID, Value) OptionalAttr_2(MessageTraceID, Value) OptionalAttr_3(MessageTraceID, Value) ... If a MessageTrace 'MessageTraceID' has a value 'V' for the Optional "Attribute" Attr_k, then there is a row (MessageTraceID, V) in the table OptionalAttr_k pros :
   • Ease of adding new "Attributes" : costs only to create a new table
   • can SELECT on any "Attribute" cons :
   • Multiple JOINs to retrieve the data -- performance problem
   • Difficulty of using the DataModel (the queries are much more complicated)

These are the 3 Datamodels I can think of... Do you know of other ones ?
Are there others pros/cons I forgot ?
Which one do you think best appplies ?

Thanks in advance !!

SerGioGio Received on Fri May 31 2002 - 18:18:16 CEST