Goodmorning,

I have to continue to work on a existing ERP program with an existing FB database. I don't like the design very much, looks messy and the original designer didn't think twice a lot from what I see.

Example, when I take a look at the small lookup tables used to store payment conditions, currency codes, titles, etc...., the primary key is always VARCHAR(4) to either store a generator number of a user defined CODE. But most of the time the value is just coming from a generator and with '0' as prefixes, like '0001', '0002'. It doesn't make much sense to me as the code is not shown anywhere. I guess the design would be better if a SMALLINT was used, am I right ? 2 Bytes instead of 8, correct ?

The PROJECT table has a primary key VARCHAR(7) and more worse the SUBPROJECT table has composite primary key made up of the PROJECTNUMBER VARCHAR(7) AND A UNIQUE SUBPROJECTNUMBER for each project, VARCHAR(4).

So JOINs look like this :

JOIN PROJECT PR ON PR.PR_NR = <AColumn>

JOIN SUBPROJECT SU ON SU.SU_NR = PR.PR_NR AND SU.SU_SUB = <AColumn>

The quotation table has only 30.000 records and is not performing too good. That SUBPROJECT JOIN has to JOIN on 2 fields because of the composite primary key. VARCHAR(7) + (4) = 11, sounds like 22 bytes to me, instead of 4 bytes of an Integer.

I think a better design would be to just define a INTEGER field as primary key for both tables with a meaningless value from a generator. That way the join would look like this :

JOIN PROJECT PR ON PR.PR_ID = <AColumnID>

JOIN SUBPROJECT SU ON SU.SU_ID = <AColumnID>

Am I right in my thinking that this is slowing down the whole query ?

Thanks in advance !

Best regards,

Steve