4GL Patterns #8 – Table Patterns

Ken Downs classified database tables into several patterns. These are grouped along the lines of number of columns, number of rows, transient vs. permanent:

• Reference Table – small number of columns, small number of rows, permanent. e.g. U.S. States. Also see Reference Table Deployability
• Small Master Table – small number of columns (but more than a simple reference table), small number of rows, permanent. Master tables hold data, rather than look up information. e.g.
• Large Master Table – transactional
• Cross Reference – describes has-a, many-to-many and other relationships

Ken goes on to suggest the types of primary keys for each type of table, e.g. Single-column CHAR primary keys for Reference and Small Master tables; Auto-assigned integers for Large Master tables; and Compound keys for Cross Reference.

I would like to add that tables should be explicitly annotated for their patterns and expected usage.

Applicability and Consequences

• Code generation tools can then intelligently cache slow changing patterns in-memory, during application start-up, or upon first-use.
• Character primary keys can be used as shortcuts for rapid data entry
• Look up controls can be auto-generated when reference tables are used in foreign keys