Prior to using any functions in the system, the tables (where datasets will be stored) must first be created and configured. Even through the out-of-the-box includes the standard tables that can be used for basic functionalities, these tables can be expanded to include specific columns and fields best suited for each customer. Furthermore, additional tables can be created and configured to store custom-specific datasets. This configuration specifies how to create and update tables.
There are a number of different table types that are utilized by the system, each with its own use and requirements. To ensure that the correct table type is used for its intended purpose it is important to understand the different types of data table available:
- Location Tables Location Tables are for strictly location-referenced datasets. In this type of table, the data is identified strictly by location (route with "from" and "to" point boundaries). An example of location-referenced data is road surface distress data that is collected by route and start/end mile point. This type of table and the windows associated with it are created via the Tables window, which is found in the Utilities menu of the System module.
- Inventory Tables This type of table is mainly for the data primarily identified by its asset ID number. An example of inventory data is a data table for radios where each radio has its own identification number. This type of table is first created in the Asset Type window by specifying the name of the inventory table and then the windows associated with it are created via the Tables window. Inventory data tables are displayed via a data view window specifically for inventory that is supported by status and class code windows. (If the implementation also includes a maintenance management system, two additional windows for periodic maintenance are also associated with the inventory data table.)
- Inventory Details Table This type of table normally works with Inventory Tables to store multiple child dataset for the Inventory, most commonly, linear location data (i.e., the route and measure(s) where the asset is located). This type of data cannot be efficiently stored and managed in an Inventory Table. For example, a bridge is normally stored in Inventory table, but the bridge's linear location, when spanning 2 or multiple routes, cannot be efficiently stored in Inventory table, and will be stored in Inventory Details Table.
- Setup Tables This type of table is to provide data source to T-list columns (see Section 2.1). The data normally does not have any location associated with it; each record is identified by an internal ID number. The information in T-List tables is typically used in drop-down lists throughout the system, and enhances the information provided by a record. This type of table and the windows associated with it are also created via the Tables window.
- General Tables These are tables that are defined by the user in the Tables window. They are used for a wide variety of purposes specific to a particular implementation. As the name hints at the General type table does not have specific use and can serve situations whenever the other table types are not suitable. One key point to be aware of is that this type of table does not provide a Primary Key by default so when this table is created one must add a Primary Key to the table.
Full information on Tables can be found at this location in docs.agileassets.com.
The difference between each type of table is summarized in the following table.
Table Type | Purpose | Typical Use | Primary Key or Unique Index columns | Can be Geo-enabled | Support LRS |
|---|---|---|---|---|---|
Location | Data is strictly identified by Location (Route and measure) |
| LOC_IDENT | Yes – add GEOM column | Yes – by default |
Inventory | Data is identified by its asset number | Any asset inventory | XXX_ID | Yes – add GEOM column | Not recommended – Use Inventory Detail table. |
Inventory Details | When one asset in the corresponding INVENTORY table has multiple associated child records, e.g., linear location | Any asset inventory details such as linear locations | XXX _DET_ID | Yes – add GEOM column | Yes - by default (LOC_IDENT included) |
Setup table | Each record is identified by an internal ID number | Any list values | XXX_ID | Yes – add GEOM column | Not recommended |
General | Any | Any | Created by user | Yes – add GEOM column | Not recommended – Use Location table |
The different types of tables have specific methods of creation, these are described in the subsections below.
