I have a table called LEDGER with records that look like this:

iLEDGER integer -- primary key of LEDGER table
iACOUNT integer -- primary key of ACOUNT table
iITEM integer -- primary key of ITEM table
iBATCH integer -- primary key of BATCH table
cASSOC integer -- association (company) code
cType character(1) -- transaction type code (C, D, F, G, I, P)
yAmount currency
tEffective datetime

There are a few other fields but this is enough for discussion.

The two character fields are redundant. The association code could be determined by going to the ACOUNT header record. The type code could be determined by going to the ITEM header record.

My experience is that SQL selects can slow down dramatically when you go beyond two tables being joined. By adding 3 bytes to the LEDGER record I eliminate as many as two tables from some joins. My testing shows that the system is faster this way.

BTW, I currently have an index on LEDGER.cType and I will be removing it in the next version since further testing indicates that will give me faster response on the network.

FYI

Peter Robinson