18) GEN, CA GEN, Coolgen and design best practice

Use of a running sequence number as the primary key instead of a logical key gives rise to the following issues:

·       For new creations the access is always towards the end of the table

·       Contention in an multi-user environment for accessing the last sequence number

Best Practice:

1.   One solution is to a common table, which contains only two attributes: table name and last sequence number. Being a small table, this should resolve the contention problem to a great extent.

2.   Instead of creating the primary key ASIS, the key is inverted and created. This will resolve the creation of new records towards the end of the table.

For eg:

If the primary key is a 6-digit number, the new records are created as follows:

·       100000

·       200000

·       ...

·       900000

·       010000

·       110000

·       210000

·       ...

Note: One could also use a random number generator to create unique primary key. See a post to create a random number using program..

17) GEN, CA GEN data modelling best practice

Denormalization of tables whenever the access equals or exceeds three levels but can be avoided by having additional attributes.

Eg: To get attrib1 table T1 and attrib3 from table T3 the normal sequence would be:

Read T1 Where A1 = local view lcl_a1

When Successful

            Read T2 Where current view and

                        A2 = local view lcl_a2

            When Successful

                        Read T3 Where current view and

                                    A3 = local view lcl_a3

                        When Successful

                        ...

End Read

Instead by storing the extra attrib1 attribute in Table T3, these three reads can be avoided.

Note: There is of course the need to update this field as when the field is updated in table T1.