Table Relationships

As your database grows, you may want to classify your features more efficiently. Instead of typing values manually (e.g., city, town, village) each time you add a new place, you can create a lookup table and link it to your existing places table.

This improves data consistency, prevents typing mistakes, and ensures your classifications follow a standard list.

Step 1: Create a Lookup Table

You’ll start by creating a new table called place_types.

1. In pgAdmin, navigate to kartoza_postgis → Schemas → public → Tables.

2. Right-click Tables, and select Create → Table.

3. In the General tab, fill in:

   
   

   Property	Value
   Name	place_types
   Owner	kartoza_postgis
   Schema	public
   Tablespace	pg_default

   
   

4. Open the Columns tab and add:

   
   

   Name	Data Type	Length	Not Null?	Primary Key?
   id	integer	—	x	x
   label	character	128	x

   
   

5. Click Save.

Image credit: pgAdmin

Step 2: Add Records

Now add some categories to classify your places.

1. Right-click the place_types table.

2. Select View/Edit Data → All Rows.

3. Click the Add Row button and add values such as:

   
   

   id	label
   1	City
   2	Town
   3	Village
   4	Settlement

   
   

4. Click Save Data Changes.

Your lookup table is now populated.

Image credit: pgAdmin

Step 3: Add a Foreign Key Field

The places table needs a field that links each place to a type.

1. Click the Query Tool from the top toolbar.

2. Run the following command into the SQL Editor:

   
   

   ALTER TABLE places
   ADD COLUMN place_type_id integer;
   

   
   

This new field will store the ID from place_types.

Image credit: pgAdmin

Step 4: Create the Relationship

Now enforce the relationship in the database so that place_type_id must match a value in your lookup table.

1. Run this SQL in Query Tool:

   
   

   ALTER TABLE places
   ADD CONSTRAINT fk_place_type
   FOREIGN KEY (place_type_id)
   REFERENCES place_types (id);
   

   
   

Your tables are now formally linked in PostGIS.

Image credit: pgAdmin

Step 5: Configure in QGIS

Now that your lookup table is in place, you can connect it to your main layer.

1. Go back to QGIS.

2. Right-click your PostGIS connection → Refresh.

3. Load the new place_types table.

4. Open the Layer Properties for the places layer.

5. Go to the Attribute Form tab and select the place_type_id field.

6. Change its Widget Type to Value Relation.

7. In the widget settings:

   
   

   Setting	Value
   Layer	place_types
   Key	id
   Value	label
   Allow Null	Optional ✓

   
   

8. Click OK.

Now, whenever you add or edit a place, QGIS will show a dropdown list of place types instead of a free-text field.

Image credit: pgAdmin

Step 6: Test the Relationship

With the widget configured, you can now test how the relationship behaves during real editing.

1. Select the places layer.

2. Click the Toggle Editing button.

3. Select the Add Point Feature tool.

4. Click on the map at the location where you’d like to place your new feature.

5. On the Attribute Form you should now see a dropdown menu for place_type_id.

6. Fill in the relevant information, then click OK.

7. Click Save Layer Edits from the top toolbar.

Image credit: pgAdmin

You can now return to pgAdmin to verify the updated field.

Image credit: pgAdmin

Your places data is now linked to a clean, controlled classification system, an essential step toward building well-structured, professional spatial databases.

---

Next up: Learn the basics of SQL & Spatial Queries so you can filter, analyse, and explore your PostGIS data.