Kbase P25833: Dynamics. Good Database Structure Principles.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/26/2003 |
|
Status: Unverified
GOAL:
Dynamics. Good Database Structure Principles.
FACT(s) (Environment):
Dynamics 2.0A
FIX:
1. Make sure your database is properly normalized · no replication of data without a good reason and a guarantee that it is kept properly in sync.
2. Make sure your data can be accessed by straightforward queries, not requiring procedural 4GL code to identify related records.
3. Avoid arrays in the database tables, or use them only for self-contained server-side logic, never for displayed fields.
4. Restrict tables to a reasonable number of fields (well under a hundred); divide related data up into multiple tables that can be related on a one-to-one or one-to-zero-or-one basis.
5. Structure your tables and fields to allow simple database transactions, rather than forcing several tables to be updated in a single transaction.
6. Structure your tables so that you don·t require database record locks to be held; this is not possible in a distributed environment · use flag fields or other techniques instead.
7. Always have at least one index on every table.
8. Always have a unique and unchanging key on every table, a single-component key if at all possible Take advantage of the Dynamics Object ID convention if at all possible.
9. Always have an index on foreign key fields to facilitate joins on the foreign key.
10. Never store RECIDs or ROWIDs in the database or try to rely on them as join fields.
11. Don·t rely on Progress field validation in the User Interface; don·t ever expect to do CAN-FIND validation that requires a database access on the client.
12. Get a site number for your database from POSSE before doing any development work that might involve sharing repository or application data with another database or with the POSSE web site