Part 4 CMD Tables

This section describes the structure and use of the various tables in the CMD component of the Meta system. These tables are stored in the Meta database. The structural information contained in this section can be obtained from the system by running the TMD report Contents of Tables from within the Meta database.

Each table is described in the same way.

Field: The name of the column in the table. The column(s) forming the Primary Key of the table is emboldened.

Type: The Access type of the column.

Scale: The name of the Scale in the system to which the Field is linked.

Purpose: A brief description of the intended use of the information stored in the column.

Note that each Field and Scale combination will appear in the M Field Scale table.

Table M Scale

Every Scale referenced anywhere in the system must have an entry in this table. Entries in this table are made using form Scales. Information can be retrieved from it using function M_Scale_Attribute. Further columns could be added since attributes are retrieved by name.

Figure 7: Form Scales

The status attribute of a Scale indicates the type of use made of the Scale within the system. Permission to alter the definition and content of a scale is linked to its status. New status values can be defined by the application for its own use, but the existing ones should not be changed as the facilities in the Meta Library makes use of them.

Figure 8: Scale Status

Scale Type Codes

A Scale has a type code which also determines the additional information stored for it.

1. A coded scale using numeric (positive integer) codes
2. A coded scale using text codes
3. A numeric scale using long integer values
4. A numeric scale using double precision floating point values
5. A scale containing dates
6. A scale using free text
7. A scale using internal logical (1-bit) values

Figure 9: Scale Types

Types 1 and 2 must have entries in the M Code table for all allowed values, and only values in this table are allowable for the corresponding fields. The table also holds labels and descriptions for each code.

Types 3, 4 and 6 can have entries in the M Code table for special values (eg missing), but no other entries.

No additional information is stored for the other types of scale. The CMD forms ensure that only values appropriate for a scale can be entered into the corresponding fields.

Table M Code

Every value to be allowed for scales of types 1 or 2 must be defined in this table. Special codes may also be declared for types 3, 4 and 6. Further description fields may be added, for example to summarise the coding rule for the code, or to provide text for particular reporting contexts.

Entries in this table are made using form Scales (see Figure 7), or with routine M_Add_Code. Values can be retrieved with routine M_Attribute. Further columns could be added since attributes are retrieved by name.

Figure 10: Special Codes

Note that all Scales require entries in the Code field, even text ones, and the numeric code must be unique. This is forced on us because Access will not allow Nulls in a Primary Key, but it allows us to assume that the numeric code always exists, which simplifies some internal operations. Text Scales require that the Code Name field be completed (and unique) and it may be filled for numeric Scales (though it is not used).

Special Codes

The idea of a Special Code is a generalisation of the relational concept of Null. If a code has a non-blank Special Code attribute then it is understood as not being the same type of information as is usually stored for the scale (it is an exception). For example, it is common practice to use 99 as a code in a 2-digit field (such as Age) to indicate that the required data is not available. With this mechanism any code can be declared to be special, and different types of specialness are supported. Specialness is not limited to coded scales, but special values can be defined for any numeric and text scale. Special values are not provided for date scales: instead date fields should generally be accompanied by a data status field. It is intended to associate specific semantics with particular Special states, but nothing has been implemented yet.

Current Codes

The Current Code field is intended to support the situation where a particular code for a scale exists in the data tables but is not valid for new data. In particular the standard treatment for combo and list boxes on forms is to exclude codes which are not Current.

Missing values and Nulls

The relational system supports the single concept of Null to mean no data in a field, and this can be an economical use of storage. The CMD system allows a code in a scale to be declared as the Null Equivalent for the Scale. Only a Code which has a non-blank Special Code status can be chosen as the Null Equivalent for a Scale. Routine M_ConvertNull can be used to convert between Nulls and the corresponding code, and if routine M_Attribute is called with a Null value for a Scale with a Null Equivalent defined it will return the attribute of the equivalent code. Thus Null values in a field in the dataset can be associated with a Code in the corresponding Scale and inherit the attributes of that Code.

Table M Field Scale

This table contains the links between the Field names used in the Host application and the Scale names in the Meta database. Every Field name used in the system must have an entry in this table: fields derived in queries and data controls on forms and reports are included. Note that the name of the Field is not linked to a source object, so the same link is used for the given field name, wherever it appears.

Figure 11: Form Field Links

With this table we are able to link more than one Field to the same Scale. It allows calls to the CMD routines to be made using the Host Field names, which the routines link to Scales using entries from this table. Function M_Scale_Name returns the Scale name for a given Field.

Entries in this table are made using form Field Links. The form imposes the rule that a Field with the same name as a Scale must be linked to that Scale, and that the label and description must be empty for such a field. Routine M_Attribute looks first in this table for labels and descriptions for a Field name which is not a Scale, then in the linked Scale if the requested attribute is blank here (or does not exist).

Table M Scale Map

This is a many-to-many table linking Scales for which a Recode operation is defined. The purpose is to map values from fields using the To scale into codes in the From scale.

The link between pairs of Scales is ordered, because the mapping is a many-to-one operation between the codes of the Scales. The number of active codes in the To scale will (usually) be fewer than those in the From scale. An example is to link Hospitals to their Districts, or to classify countries into continents.

Figure 12: Form Scale Maps

Entries in this table are made using form Scale Maps.

There is no restriction on the number of mappings in which a scale can appear.

It is allowed for the same pair of scales to appear in more than one mapping, though it is unusual for this to be useful.

It is allowed for both the forward and reverse mappings to appear for any pair of scales. Logically it is not possible for the From scale to be covered in both mappings unless they are both one-to-one mappings. This is only useful to convert between equivalent but different codings - such as between PHLS and WHO diagnosis coding. In such a case the second map is the inverse of the first, so is strictly redundant, but the Meta system does not provide a facility to use a Recode backwards.

It is allowed for a scale to map into itself, though it is rare that this is useful.

The Unmapped code indicates the treatment for handling codes which are valid for the From scale but not included in the map specification. The two most useful options are to pass the code through unaltered, or to convert it to Null.

Figure 13: Unmapped Codes

Table M Code Map

This table indicates the links between codes for a Recode. The Primary Key of this table is the combination of Recode Name and From Code, which means that a code from the From scale can only appear once within a given Recode. There is no such constraint on the To Code, so that several From codes can be mapped to the same To code (as is obviously required).

Note that the mapping is defined between the numeric codes of the Scales, whether they are numeric or text.

Entries in this table are made through form Scale Maps.

Go to - - - -

Page last updated 18 June, 2003.