Re: Table Design Advice - normalizing

From: James <jraustin1_at_hotmail.com>
Date: 25 Oct 2001 20:51:50 -0700
Message-ID: <a6e74506.0110251951.50dd2dd8_at_posting.google.com>


> I have potentially 4 entities which are all of the same "master
> entity" type (if you can call it that). Each entity shares the same
> subset of 6 fields, but 3 of the entities have additional fields.
>
> ex. Entity SimpleFP : fields - LongDesc,ShortDesc,ActID,Qty,Year,Month
> Entity ComplexFP: fields -
> LongDesc,ShortDesc,ActID,Qty,Year,Month,UnitPrice, ISDate.

A master table could hold that which is common to all entities, then a separate table would be needed for the specifics of each entity.

T_Master
 LongDesc,
 ShortDesc
 ActID
 Qty
 Year
 Month  

T_EntityType1
 MasterID
 UnitPrice
 ISDate

T_EntityType2
 MasterID
 FieldX
 FieldY

In an object-oriented database, like XDb, the master table would be the base class and the remaining derived classes.

Master
 .CommonProperty1
 .CommonProperty2

Entity1 [Master]

 .CommonProperty1
 .CommonProperty2
 .SpecificProperty40

Entity2 [Master]

 .CommonProperty1
 .CommonProperty2
 .SpecificProperty50

In the above case, there is no equivalent of "joining tables" to retrieve all the info about any specific instance. Also any instance can have fewer or more properties than defined in its class. This allows you to handle that rare/odd case without adding a property to all other instances. Download your free copy (260 KB) from http://www.xdb1.com Received on Fri Oct 26 2001 - 05:51:50 CEST

Original text of this message