Naming convention for foreign keys

[jpdupuy]

All attribut names referencing value lists should be prefixed by "fk_" such as horizontal_positioning ->fk_horizontal_positioning

[sjib]

No, fk_* is reserved for attributes referencing another data table via the obj_id, not the value lists.

There are other ways to find out whether an attribute has a value list if this is what you need.

The structure and naming convention for referencing value lists is like this:
ALTER TABLE tdh_od.pipe_section ADD CONSTRAINT fkey_vl_pipe_section_horizontal_positioning FOREIGN KEY (horizontal_positioning)
REFERENCES tdh_vl.pipe_section_horizontal_positioning (code) MATCH SIMPLE

[jpdupuy]

It is in contradiction with what is written in the TEKSI development guide 👍 https://github.com/teksi/Home/wiki/TEKSI-Developer-Guide#modules-repositories-

For value list relations fk_vl_*: CONSTRAINT fkey_vl_access_aid_kind FOREIGN KEY (kind)
REFERENCES tww_vl.access_aid_kind (code) MATCH SIMPLE
ON UPDATE RESTRICT
ON DELETE RESTRICT,

[ponceta]

Mmmmh QWAT and QGEP have two different approaches on that subject, indeed. (fk_status vs status as an example)

There is no mandatory database requirement to prefix / suffix fk_ / _fk / _id

But it is still a good practice in usual relationnal database implementations to make it visible somehow.

Here we face a huge renaming work if we go for the fk_ way for TWW. And furthermore this is a change of naming from the INTERLIS datamodel.

Therefore I would recommend :

• To think carefully of the pros and cons of such an approach
• If not strong pros, to stick to the actual implementation (I'm not even sure we need the fk_ prefix on data owners etc... exept if the original INTERLIS model defines it)
• To enhance the visibility of data provider constraints on QGIS side

• If required, expose relationnal fields like fk_status in the edition views, these namings can be set at will.

[jpdupuy]

@ponceta Thanks for your analysis
I think it is time to standardize all products (TWW, TWA, TDH,...) and stick to the developer's guidelines ... or change the guidelines