Newbie Data storage Question

Hi !

I need to store "Message Traces" in a Oracle Database. 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
   • 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. "CLOB 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. "Multiple-Table Design" : Creating tables like MessageTrace(Attr_1, Attr_2, Attr_3) OptionalAttr_1(MessageTrace, Value) OptionalAttr_2(MessageTrace, Value) OptionalAttr_3(MessageTrace, Value) ... If a MessageTrace 'MT' has a value 'V' for the Optional "Attribute" Attr_k, then there is a row (MT, 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 Thu May 30 2002 - 06:32:01 CDT