Re: Newbie Data storage Question

What about 3 tables?
1 For the Message trace
2 For the attributes types
3. For the value in a message of an attribute Like
create table Message ( messageID number not null); //messageID is your primary key
create table attribute_types (attribute_typesID number not null, description varchar2(XX)); //attribute_typesID is your primary key create table Message_attributes( Message_attributes number not null, messageID number not null, //your key to the Message Table attribute_typesID number not null, // your key to the type of attribute value varchar2(xx) not null) //Your attribute value

Then you use the least space, you can query on it much easier than the attrib_1,2, etc. and you don't have to parse it out. Jim

"SerGioGio" <serge.malonga_at_supelec.fr> wrote in message news:e42147c6.0205300332.149c13b_at_posting.google.com...
> 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 - 09:50:28 CDT