PostgreSQL/GIS table relations in QGIS 1.8.0

Question

I am building a database in PostgreSQL 9.2 with the PostGIS 2.0 extension. The database consists of a schema called Water which contains water related geographic features (as example, boreholes and water filters as points with coordinates). Each feature type, eg. water filters or boreholes, is in a separate table. In other words, water filters are in a table named water_filters and boreholes are in a table named Boreholes and the tables are in a schema called Water.

In the same database I have a schema called maintenance with non-geographic tables. The maintenance tables houses the maintenance records of the respective geographic features. The maintenance table for water_filters will house, for example, a unique number/name/identifier for a water filter and the latest service date.

Thus far, I have selected for the geographic features (eg water filters) a unique number/name/identifier as the primary key. For the corresponding maintenance table the number/name/identifier of the geographic features was selected as a foreign key.

My questions:

1. Is this the correct way to define relations between two tables in PostgrreSQL/GIS?
2. How do I view the relations in QGIS 1.8.0?

Thanks in advance

Answer 1

1.Is this the correct way to define relations between two tables in PostgrreSQL/GIS?

Yes, this is the right way to create relationships between tables (not only in PostgreSQL / PostGIS). The relationship is a concept ; you should know that there is a relationship between these two tables because you design the database. You can work with a tool for design database as DBDesigner and you will get something like that :

enter image description here

You can also create some database views which will contain the join and data from your 2 tables, it's very usefull.

2.How do I view the relations in QGIS 1.8.0?

How do I view the relations in QGIS 1.8.0 You cannot in Quantum GIS (but there is maybe a new plugin for that...).

• PyMapr: Thanks for the reply Benno. For database design I decided on pgModeler. I couldn't find a plugin for viewing related tables in QGIS. However, until such a plugin makes it appearance the join table option in QGIS will do the job. Thanks again.