Kbase P25830: Dynamics. Indexing Guidelines
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/15/2008 |
|
Status: Unverified
GOAL:
Dynamics. Indexing Guidelines
FACT(s) (Environment):
Dynamics 2.0A
FIX:
A few comments are in order where indexes are concerned. First, no table should be defined without at least one index. This is always good policy (not observing this is the cause of many performance problems), but in particular Dynamics will have problems dealing with tables with no index.
Never make a key that has everyday meaning in it the only unique identifier on a table. A meaningful value is one that cannot be forced to change. A person?s name, or company name, or even a numeric key whose value is not a completely arbitrary and never-changing sequence value, are all problematic choices for key
values. For example, textbooks on relational theory and SQL [not to mention Progress] will 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. However, a definition such as this is inherently denormalized, because the Sales Rep?s initials, which are stored in the field used as the key, are stored in two different tables, and could possibly change (as the result of marriage, for instance). SQL is full of rules about how to deal with such changes, whether to cascade them or not; Progress does not even have the benefit of such built-in rules, forcing you to build them into your database triggers or business logic. This is not good.
Every table should have a unique identifier. That unique identifier should be an unchanging sequence value that has no external meaning at all, and should never be expected to change. 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. It is worth noting that the Dynamics utilities for defining deployment datasets, identifying repository or application data that must be deployed with an application, will not deal well with a sole unique key whose value has changed. Dynamics supports a specific mechanism for defining keys called Object IDs, which are discussed in the ?Object IDs and Site Numbers in Dynamics? section later in this chapter.
Cascading data items (such as the SalesRep initials) down from parent to child is sometimes necessary, but keep in mind that this really is a form of denormalization, and is therefore well worth avoiding. Keep in mind also that, if your intention is to provide a meaningful piece of information in the child table (the ID of the Sales Rep in this case) in order to avoid having to retrieve the associated parent record, putting the key there may not accomplish what you want. In this example, you are very likely going to want to display the actual Sales Rep?s Name when displaying a Customer record, and to get that you have to join to the SalesRep table anyway. So you avoid potential headaches if you forget about using the SalesRep initials as a cascaded key value and instead use an arbitrary numeric sequence number to join the two tables; then you don?t need to worry about cascading changes.
Another couple of points on indexes and defining foreign key relationships:
1. Always put an index on a Foreign Key field that involves only that field (Customer.SalesRep, or more properly, Customer.SalesRepSequence in our example). That way, relationships such as Customers for a SalesRep can always be determined efficiently.
2. Never use the RECID or ROWID of a record to relate records. RECIDs and ROWIDs are not preserved across database dumps and reloads, and now with support for multiple storage areas, they are no longer unique across the database.