Using the Table Relations page

This guide explains how to use the Table Relations page in OneGov. This tool is very useful when creating datasets with tables that you do not know how to join.

Navigating to the Relations Page

  1. Click "Customizer" under the "User tab
  2. Click "Databases" in the "Definition" section
  3. Click "Relations" in the "Customizer Target" row

How to Use the Relations Page

This page is the easiest way to look at a table and see what other tables are able to be joined. This can be very helpful when working with a new table that you are unfamiliar with or if you just need a refresher on what field to use when joining two tables. This page is split into two sections: "Choose", and "This table is related to these tables".

The "Choose" section contains a drop down which allows you to choose which table you want to view. Once you choose a table from the list, the section below will update to show the relations on that table.

The section labeled "This table is related to these tables" has three columns: "That Table", "This >> That Column(s)", and "Constraint". The column labeled "This Table" contains the names of all of the tables that have a relation to the table that was chosen in the drop-down above. The column labeled "This >> That Column(s)" lists the columns in both tables that should be used to join those tables. The records in this column follow the format "ColumnName1" >> "ColumnName2" where "ColumnName1" is the name of the column from the first table and "Column Name2" is the name of the column from the second table. The column labeled "Constraint" contains the name of the relationship in the database. This column is not needed for creating a join.

Example

If I choose the permitapps table in the first section, the second section would look similar to this (though with many more rows):

That TableThis >> That Column(s)Constraint
addressesappid >> appidfk_addresses_permitapps
permitrecommendationsappid >> permitidfk_permitrecommendations_permitapps

In this example, the table shows that the permitapps table can be joined to the addresses table on the appid column. It also shows that the permitapps table can be joined to the permitrecommendations table on the appid column from the permitapps table and the permitid column from the permitrecommendations table.