IMAGE/SQL: Reliable and Fast Database

Today it is known as IMAGE/SQL because it includes an SQL (Structured Query Language) frontend and client-server support for PC accessors.

Database Design

• Use masters for unique data items like customer numbers.
• Use details for repeated data like customer purchases.
• If a master record needs an alternate key, make it a detail and use an automatic master.
• If the information in a detail dataset is volatile, avoid more than two paths into it, and avoid sorted paths, except where entries are put in chain order. Remember that IMAGE now allows any data-type as a sort field (sort fields used to be limited to unsigned data-types). If the information is relatively static and frequently accessed by on-line users, add more search paths into it.
• Get Suprtool for fast serial scans and eliminate infrequently used search keys.

Polishing Database Design

• For numeric fields, use J1 for fewer than five digits; use J2 for fewer than ten digits; otherwise, use a P-field (packed-decimal).
• Types X, U, P, and Z give the best hashing results, especially if the key length is greater than six bytes.
• Avoid keys of type I, J, K, and R.
• Store dates in yyyymmdd format, either as a J2 (32-bit integer) or as an Z8 (do not leave out the century).
• Assign a primary path to every detail dataset; select the most frequently used path with more than one entry per chain.
• Try to keep master datasets less than 80 percent full.
• Never use a power of 2 as a master capacity (for example, 32, 1024, and so on).
• Avoid tricky data structures that you cannot use in all your tools.

Database Maintenance