Data structure for agency assets

The database tables that store agency defined assets are similar to the standard asset tables, but with some important differences. For standard asset types, each asset has records in two different tables:

  1. The COMP table is a central table that stores information on all assets in the database, regardless of the asset type.
  2. Each asset type also has its own concrete table, such as COMPVEH for vehicles. The COMP table stores basic identifying information for the asset, and the concrete table stores information that is specific to the asset type. For example, the COMPVEH table stores information such as a vehicle's model number, engine type, and transmission type. The two records are linked through the asset key (COMPKEY column).

For agency defined asset types, each asset also has a record in the COMP table, but there is not a separate table for each asset type. Agency defined assets are stored in these tables in addition to COMP:

  • COMPAGENCYSIMPLE stores all agency defined simple assets.
  • COMPAGENCYNETWORK stores all agency defined network assets.
  • COMPAGENCYSEGMENT stores all agency defined segment assets.
  • COMPAGENCYLINEAR stores all agency defined linear assets.

The COMPTYPE column in each of these tables identifies the specific asset type.

In addition, some columns that are included in the standard asset type tables are not included in the tables for agency defined assets. These columns are not included in the agency asset tables:

  • ADDRKEY
  • ADDRQUAL
  • EXPBY
  • EXPDATE

These columns are all available for agency defined assets, because they are included in the COMP table. To access these values for reports, you can join the agency asset table to the COMP table based on the asset key.