What is a Data Dictionary?

A Data Dictionary holds meta data, or 'data about data', for an application database, and is capable of holding more information than is available in the database's own INFORMATION_SCHEMA.

No application can perform efficiently unless the underlying database has been properly designed. The application requires knowledge of the database structure so that it can issue the correct SQL queries. This is especially important in RADICORE as each query is constructed at runtime using standard algorithms. Information about the database structure, which is known as metadata or 'data about data', can be obtained by querying the system tables within the database (or the INFORMATION_SCHEMA in modern databases) but this information is limited to what the database requires in order to function, and is only a subset of what the application as a whole may need. A Data Dictionary provides the means of supplying this additional information.

The ability to hold information which goes beyond the basic metadata can provide numerous benefits. Extra comment fields associated with each column, table and relationship help the developers to maintain a clear understanding of the role they play in the application, as maintenance needs and enhancements force tables to evolve. Entity relationship diagrams (ERD), while central to such development efforts, don't go far enough when it comes to explaining the actual use of particular columns. Some ERD design tools do allow you to store metadata in them, but this forces anybody who wants to look up metadata definitions later to use those specific tools. Moreover, descriptions stored in design tools, while adequate for those involved in the design process, may not meet the needs of developers performing maintenance or creating enhancements after the application goes live. Consequently, the usage and meanings of columns and tables typically get modified, lost, or obscured over time. A simple data dictionary can help you monitor and maintain this essential information.

The Data Dictionary supplied with RADICORE assists in the development process by automatically generating the objects in the business layer. It achieves this in the following stages:

  1. IMPORT - import the details of existing application databases into the dictionary database. If any application tables are modified the import procedure can be run again to resynchronise the dictionary with the database. This will deal with new columns, dropped columns and modified columns. If a column is modified any existing additional information is retained unless it becomes invalid.
  2. EDIT - edit the details by including whatever additional information that may be of use to the application. This "additional" information includes such items as:
    • For individual tables turn Audit Logging either ON or OFF.
    • For each relationship (one-to-many) add the details of the child (many) table to that of the parent (one) table.
    • For foreign keys identify which field is to be retrieved from the parent (foreign) table and included in the output for the child table. This information is used to include the relevant JOIN clause into the SELECT query for the child table so that the relevant data from the parent table is automatically included in the result.
    • If there is more than one relationship between the same two tables, or a table is related to itself, it is also possible to specify alias names in the data dictionary to aid in the construction of the JOIN statement.
    • For string fields make the contents either upper or lower case.
    • For numeric fields specify maximum and/or minimum values.
    • For any field define which type of control (text box, dropdown list, radio group, checkbox, etc) is to be used in the HTML output.
    • For any field define it as NOEDIT (read only) or NODISPLAY (invisible) in the HTML output. There is also a NOSEARCH option to prevent a field from appearing in any SEARCH screens, and a NOAUDIT option to prevent a field from appearing the Audit Log.
  3. EXPORT - export the details to provide the information in a format which is readily accessible to the application, thus avoiding the need for numerous or complex database lookups. These details include the field specifications, primary and unique key details, plus the details of all related tables (parents as well as children). This information is used within the table class to perform primary validation and referential integrity. Note that the first time the export process is run for a table the class for that table will be generated automatically. This provides enough functionality to perform read, write, update and delete operations on that table. The developer need not edit this class except to insert business rules that cannot be defined via the Data Dictionary.

It also has the ability to generating the components in the presentation layer for each transaction. This in the following way:

Note that the generated transactions will be basic, but can be enhanced.

Note that the Data Dictionary is a tool for developers, not end-users.

A more detailed description of the Data Dictionary used within RADICORE can be found here.

Published: 09 April 2006