Re: 1st normal form question How many tables is too many??

> Okay, I have ACCOUNTs, ACCOUNTGROUPs, TRANSACTIONGROUPs and
> TRANSACTIONS. ACCOUNTs can have TRANSACTIONs, and be members of
> ACCOUNTGROUPs and TRANSACTIONGROUPs. In addition, ACCOUNTGROUPs can
> be members of TRANSACTIONGROUPs. So far so good. Now these
> TRANSACTIONs link to backend sysetms which require various little
> tidbits of data like IDs, passwords, email addresses, etc. There is
> no consistancy to the data requirements of the various backend
> systems. So what I am considering is a BAGGAGE table. But this is
> getting kind of busy now, because BAGGAGE amy be associated with an
> ACCOUNTGROUP or an ACCOUNTGROUP-TRANSACTIONGROUP relationship (or any
> other entity or relationship) so we are getting into a fairly large
> and complex (for me anyway) set of tables and relationships. I also
> think that the BAGGAGE is potentially many-to-many so that I will need
> for example a ACCOUNTGROUP-TRANSACTIONGROUP-BAGGAGE table,
> ACCOUNTGROUP-BAGGAGE, TRANSACTIONGROUP-BAGGAGE,
> ACCOUNT-ACCTOUNTGROUP-BAGGAGE, ad infinitum (almost).

In an object-oriented database, such as XDb, the above problem can be modeled as shown below, where "." is a property and "->" is a reference to an object. In the diagram below, any of the properties are optional. XDb allows a property to have 0, 1 or many values. XDb allows a property to have any number of sub properties. For example, a transaction, when considered by itself may have propertyC, but when the same transaction is part of an account, it might need propertyI. Similarly an account by itself may need a propertyA, but when the same account is part of an accountGroup, it may need propertyL. This level of normalization is possible, but not practical, in a relational db because of the complexity and performance degradation.

Acnt
 .PropA
 .Trans
   ->TransX

      .PropI
   ->TransY

      .PropJ
      .PropK

AcntGrp
 .PropB
 .Acnt
   ->AcntX
     .PropL
   ->AcntY
     .PropM

Trans
 .PropC

TransGrp
 .PropC
 .Acnt
   ->AcntX

     .PropN
     .PropO
   ->AcntY
     .PropQ

A database and application with a similar solution involving sub properties can be downloaded from:
http://www.xdb1.com/Example/Ex043.asp (260 KB) http://www.xdb1.com/Example/Ex041.asp (260 KB) Received on Fri Oct 19 2001 - 23:21:52 CEST