Newbie database design

From: SerGioGio <>
Date: 31 May 2002 09:18:16 -0700
Message-ID: <>

Hi !

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

Original text of this message