Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

> ## Indexing guidelines

> When designing a database, there are indexing guidelines that you should consider. First, no table should be defined without at least one index. This is generally a good policy. Not observing this causes many performance problems. However, Progress Dynamics has particular problems dealing with tables that have no index.

> Try not to make a key that has a real meaning the only unique identifier on a table. There should be a key whose values cannot be forced to change. Any name or number whose value is not a completely arbitrary and never-changing sequence value might be a problematic choice for a key value. Certainly, a value that has a good probability of changing is a poor candidate for a key.

> For example, textbooks on relational theory and SQL (not to mention Progress) routinely use examples where a meaningful key value is used as the sole join field for two tables, such as `Customer`.`SalesRep` and `SalesRep`.`SalesRep`. The sales rep’s initials are stored as the value for these fields. However, this definition is inherently denormalized because the sales rep’s initials are stored in two different tables and might change (as the result of marriage, for instance).

> Every table should have a unique identifier. That unique identifier should be an unchanging sequence value that has no external meaning at all. All relationships to the table are then based on this unique identifier. Having alternate keys that are multi-component is fine, as long as there is another “primary” way of relating entities to one another.

> Progress Dynamics supports a specific mechanism for defining keys called Object IDs, which are discussed in the "Object IDs and site numbers in Progress Dynamics" section.

> Cascading data items (such as the `SalesRep` initials) down from parent to child is sometimes necessary, such as for performance reasons, but this is really a form of denormalization and should be avoided.You might want to provide a meaningful piece of information, the ID of the sales rep in this example, in the child table to avoid having to retrieve the associated parent record. However, putting the key there might not accomplish what you want. In this example, you might want to display the actual sales rep’s name, rather than initials, when displaying a `Customer` record. To get the name, you have to join to the `SalesRep` table anyway. If you use an arbitrary numeric sequence number to join the two tables, you avoid writing code for cascading changes.

> Other points to consider about indexes and defining foreign key relationships are:

> - Always put an index on a foreign key field that involves only that field, `Customer.SalesRep`, or more properly, `Customer.SalesRepSequence` in the example. This allows you to efficiently determine relationships, such as `Customers` for a `SalesRep`, at the start of the index at the least.

> - Never use the `RECID` or `ROWID` of a record to relate records. `RECID` values and `ROWID` values are not preserved across database dumps and reloads. In fact, with support for multiple storage areas, they are no longer unique across the database.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095