Architectural Principles Vertical Tables

From PPDM Wiki

About Vertical Tables

A vertical table is a table that allows a column to contain values that reference more than one kind of information. These tables generally follow the structure Property type and Property value. For example, the table EQUIPMENT_SPEC allows you to store any kind of specification about a piece of equipment. For pumps, you might store the capacity of the pump and the speed at which it can operate. For microscopes, you might store the resolution that it is capable of resolving. For and engine, you might store the horsepower rating and number of cylinders. Each of these values can be stored by defining an appropriate Property Type.

Vertical tables are used when the number of possible data values cannot be determined at design time, or the number of data value types is very large. Vertical tables should be designed with care to ensure that best data management practices can be supported. Vertical tables are often subject to degradation of the content they contain; this is because the tables are so flexible that they are difficult to manage. Our obejctive is to create vertical tables that allow the user the highest degree of control possible, so that the data is less subject to decay.

Specific design templates have been created for vertical tables, please be sure to use them.

Property type control

Each vertical table is controlled by a reference table that controls the type of property being stored. In an equipment specification table, for example, the property type table would contain the list of specification types (diameter, length, mass etc).

The Property type reference table will contain a foreign key from PPDM PROPERTY SET. The PROPERTY SET determines which columns should be used to capture information about each possible property type.

PPDM PROPERTY SET

This table and its child PPDM PROPERTY COLUMN control the behavior of each kind of property in the business (vertical) table. For example,

Value in the reference table is MASS
- In the business table, this information should be stored in the AVERAGE VALUE column, with a preferred unit of measure of kilograms and 2 decimal places of precision.
- Note that this cannot be controlled by the database, and will need to be managed procedurally.

Value in the reference table is COLOR
- In the business table, this information should be stored in the SPEC CODE table and validated against the reference table R_COLOR.
- Note that in this case, the validation cannot be controlled by database constraints, and will have to be managed procedurally.