One of the key strengths of the InfoAsset Manager data model is the fact that object relationships are explicitly defined in the data fields. For example when defining a CCTV Survey the start and end manhole of the associated pipe must be specified. Similarly the definition of a pipe repair contains the ID of the CCTV survey that has been used to identify the requirement for the repair.
These relationships can be used to manage and report on the data, for example using SQL queries and Dashboards .
While the Collection and Distribution networks have many built-in relationships between object types, the ability to add user defined object types to networks of all types means that not all relationships can be provided automatically. The user defined relationships functionality allows connections between different object types to be made, managed and used for reporting purposes.
Relationship Types
There are several different types of relationship between objects in InfoAsset Manager. Each type of relationship provides a particular behaviour to represent the connection between the object types.
These relationships (built-in and user defined) are described below:
| Relationship type | Description |
|---|---|
|
Simple Relationship |
A simple relationship relates one object type to another object type. For example a valve survey object may be related to a valve, or a pump may be part of a pump station. |
|
Asset Relationship |
An asset relationship represents the situation where an object type may be related to a number of specified objects types (usually Assets). For example a general survey object may be related to any asset type, a property or a zone. |
|
All Node Relationship |
An all node relationship allows an object to be related to a node of any type in the network. For example a pipe has two node relationships – an upstream node relationship (US node ID) and a downstream node relationship (DS node ID). |
|
All Link Relationship |
An all link relationship allows an object to be related to a link of any type in the network. |
|
Collection Network Pipe Relationships |
Pipes in Collection networks are treated slightly differently to other object types, since they are identified differently from other types (they use US node ID,DS node ID and Link suffix fields). This means that relationships to pipe objects need to be treated as a special case. As a result, pipes in collection networks cannot be used a part of an asset relationship. For example, CCTV surveys are related to surveyed pipes using the pipe fields mentioned above. |
Relationship Purpose
Relationships can exist for many different reasons. For example, one object type may be a component of another object type, e.g. a pump is a component of a pump station. Other relationships may be more general associations, e.g. a CCTV survey being associated with a pipe.
Relationship purpose must be specified at relationship creation time. See User Defined Relationship Dialog for more details.
The Object Browser Window can be used to show and manage object relationships. On this window, related objects are grouped by relationship purpose.
|
Relationship purpose |
Description |
|---|---|
|
Component |
One object type is a component of the other. Component relationships usually exist between assets and are indicative of the fact that one asset may encompass one of more other assets. A typical example of Component relationship is pumping stations and pumps (A pumping station may contain several pumps). |
|
Association |
There is a general association between the objects. This is typically the relationship that exists between assets and surveys, repairs and incidents. A pipe may have an associated CCTV survey, and this relationship is referred to as an Association or Associated relationship. |
|
Connection |
This type of relationship defines a connection between objects. For example, a pipe is connected to a node. |
|
Empty |
Empty relationships are relationships that do not currently contain any object. For example it is possible that a particular pipe does not have any associated CCTV surveys. In that case the relationship between this pipe and the CCTV survey object type is called Empty. Empty relationships may be displayed in the Object Browser Window. |
Relationship Source and Target
For a relationship to work, it is necessary for an object type to store the information necessary to identify an object of the other type. The source and target terminology has been introduced to help understand how this works. The source object type has additional fields specified to allow objects of the target object type to be identified. Some examples are shown below:
|
Relationship Description |
Source Object Type | Target Object Type |
|---|---|---|
| Pipe - CCTV Survey | CCTV Survey | Pipe |
| Pump Station - Pump | Pump | Pump Station |
| CCTV Survey - Repair | Pipe Repair | CCTV Survey |
Relationship description naming convention is target - source. For a CCTV survey associated with a pipe, the relationship description will be pipe - CCTV survey.
This description is displayed in InfoAsset Manager :
- On the Object Browser Window
- On the User Defined Relationship Dialog
- On the Properties grid of the User Defined Objects Dialog
It is possible for many source objects to refer to the same target object, e.g. many pumps can be in the same pump station. It is important to get this the correct way round when defining relationships.
| Relationship type |
Number of source fields
|
Source field name (defaults)
|
Source fields description
|
Definition |
|---|---|---|---|---|
|
Simple |
1 |
target_id |
Target Id |
One source object type to one target object type |
|
Asset |
2 |
target_asset_type target_id |
Target Asset Type Target Id |
One source object type to many target object types, usually asset types |
| All Node |
1 |
node_id |
Target Node Id |
One source object to all nodes |
| All Link | 1 |
link_id |
Target Link Id |
One source object to all links |
|
Collection Pipe |
3 |
target_us_node_id target_ds_node_id target_link_suffix |
Target US/DS Node & Link Suffix |
One object type to collection pipe objects |
The source field names have default values as shown in the table above, but the user may choose to use other field names. This will be essential in the case where an object type is the source of more than one relationship (see below).
Multiple Relationships
A relationship allows one source object to be related to one target object. In some cases, a source object may be related to more than one target object.
One example of this is a pipe repair. A pipe repair is related to the pipe to be repaired and the work package under which the work is carried out. This means that there are two relationships with pipe repair as the source object type. This is shown below:
| Source Object |
Relationship |
Target Object |
|---|---|---|
|
Pipe repair |
Work package relationship |
Work package |
|
Pipe repair |
Repaired pipe relationship |
Pipe |
Another example of this is the relationship between a Collection Network property object and its pipes. In this case the property has two relationships: one specifying the property’s sanitary pipe and one specifying the storm pipe. In this case, there are two separate relationships between the property object type and pipe object type.
| Source Object |
Relationship |
Target Object |
|---|---|---|
|
Property |
Sanitary Pipe Relationship |
Sanitary Pipe |
|
Property |
Storm Pipe Relationship |
Storm Pipe |
A source object type can have as many relationships as desired, but each relationship will have just a single target object.
Querying Relationships Using SQL
To be able to use SQL to query relationships, it is necessary to define an SQL name for the relationship. The InfoAsset Manager SQL language already has many built-in relationship names.
For example to refer to the manhole properties when querying the Manhole repair table the manhole prefix is used to identify the joined object:
SELECT FROM [Manhole Repair] WHERE manhole.cover_dim > 500
Conversely, to interrogate the manhole repairs associated with a manhole the manhole_repairs prefix is used to find the many repairs:
SELECT id, COUNT(manhole_repairs.*) FROM Manhole
When defining a relationship, the SQL prefixes to be associated with the relationship should also be specified. This is done in the User Defined Relationship Dialog. For each relationship, there is a joined prefix (source to target relationship) and the one-to-many prefix (target to source relationship). See User Defined Relationship Dialog for more details.