Data model: dataowner and provider

[andreasneumann]

Almost any od_xx table has the two attributes "dataowner" and "provider". Currently these are only text fields. I think it would be more elegant if these could be relations to the table "od_organisation" - With the current setup there is a lot of duplication and it is harder to update the information of the dataowner and provider.

Any thoughts?

[sjib]

Grundsätzlich spricht nichts gegen eine solche Umsetzung in QGEP, in der VSA-DSS INTERLIS Version wird es aber nicht so umgesetzt – es überfordert die Leute schon so, diese Daten überhaupt zu liefern. Man müsste das dann einfach beim Export in die VSA-DSS wieder anders abfüllen (also Name des Datenherrn statt FS).
Falls man z.B. das Ingenieurbüro, das Datenherr ist oder die Gemeinde dann bei Organisation abfüllt muss man einfach gut darauf achten, diese allenfalls mehrfach aufzulisten, in der Funktion als Datenherr – das ist dann allenfalls nicht ganz die gleiche Dienststelle v.a. bei grösseren Gemeinden / Städten. So wie man die Gemeinde sowohl als privaten Eigentümer und als „Gemeinde“ Eigentümer auch schon doppelt erfassen sollte. Siehe diese Überlegungen dazu auf der DSS Webseite (Attributierungshandbuch): http://www.vsa.ch/fileadmin/user_upload/DSS/download/AH_DSS_Eigentuemer_Betreiber_2007_07_02.pdf

Falls du das so willst mit FS für Datenherr / Datenlieferant, schick mir eine Vorlage, wie ich das im SQL Create anpassen soll.

[m-kuhn]

While I think the idea is basically good in terms of database structure, what do we gain from it?

QGIS supports a widget type called Unique Values. This widget type is able to suggest values that have previously been used for a certain field. So for the user it is very easy to re-enter the same dataowner, but limited to the same layer/field.

[m-kuhn]

Andreas provides an example for Stefan how to implement this.

[andreasneumann]

Here is an example how this would be done for one table (sample od_wastewater_structure). It concerns all od_* tables.

ALTER TABLE qgep.od_wastewater_structure ADD COLUMN dataowner integer;
ALTER TABLE qgep.od_wastewater_structure ADD COLUMN provider integer;

ALTER TABLE qgep.od_wastewater_structure ADD CONSTRAINT rel_wastewater_structure_dataowner FOREIGN KEY (dataowner) REFERENCES qgep.od_organisation(obj_id);
ALTER TABLE qgep.od_wastewater_structure ADD CONSTRAINT rel_wastewater_structure_provider FOREIGN KEY (provider) REFERENCES qgep.od_organisation(obj_id);

The constraints can only be added after both tables where created, so better at the end of the SQL.

[andreasneumann]

Oliver needs to check whether this is implemented in latest version.

[sjib]

Fehlermeldung: Am Schluss wenn du die Constrain für die Fremdschlüssel hinzufügst, wird zweimal fk_dataowner hinzugefüngt. Müssste einmel fk_provider sein. (issue #43)

• ist nun in der neuesten Version 22.12.2014 korrigiert

[m-kuhn]

Waiting for Interlis Import => then update the model to the newest model by @sjib => then import and migrate and verify

[m-kuhn]

Currently there are two fields for each

• dataowner
• fk_dataowner

Not sure this is what we want?

[andreasneumann]

I believe the VSA-DSS model has the dataowner field as varchar/text, but we discussed that we would prefer the fk_dataowner foreign key to the organization table.

So, we should get rid of the dataowner column and add this to an export view, when we will need the text representation instead of the foreign key.

But the export views are not top priority, currently.

[sjib]

Discuss in meeting 19.10.2015 (https://github.com/QGEP/QGEP/wiki/Meeting-2015-10-19)

[sjib]

Take out dataowner and dataprovider field in datamodel

[3nids]

@sjib what would be the delay for this?

[sjib]

In working on the INTERLIS Export / Import we found out, that it is really challenging, when we always have to link the organization table into all the queries when exporting or importing data. So we think it would be helpful to keep the dataowner / provider as a field that we can fill with the current identifier of the related dataset in organization while exporting or importing and delete it, when we have pre- or postprocessed dataowner and provider an created the necessary fs_* (postprocess while importing) or matched the identifier of organization (preprocess in the export process).

Does this disturbs any other development that you are doing?

[m-kuhn]

In this case, wouldn't it be preferable to either create the field at the start / end of an import/export process and delete it in the end?
This way the risk of mistaking this for authoritative data when it is out of sync will be reduced.

[3nids]

@sjib sorry not following, what prevents from using views for export and trigger for import?

[sjib]

Adapted in https://raw.githubusercontent.com/QGEP/datamodel Release 26.4.2016