I'm going to set up a small db for my own use in QGIS. I can see only a few of the many tables will be spatial, but the ease of use and capacity of the widgets in QGIS are attractive. I won't writing (much) sql on the command line.
How will I; - transfer the relationships / joins between computers - share the data and with anyone else (can the joins only be reproduced via a QGIS project) - use the relationships for this dataset, as a subproject of other analysis
I understand that tables (spatial or not) can exist in postgresql/postgis but important rdb function like foreign keys are not understood by QGIS. Instead joins, default attribute values, some attribute typing, need to be defined in the QGIS widgets. These constraints then become ephemeral with the project file.
Saving the widgets as part of each layer style is I presume possible, but will not provide a robust solution I think because there will be a lot of intermediate tables.
As far as I know QGIS projects don't save to pgsql.
This earlier question explores some of the issues QGIS managing relationships and these posts from Opengis.ch are helpful too. https://www.opengis.ch/author/signedav/ But what workflows are out there?
My use case is for a bunch of data that exists almost entirely in a spreadsheet, it is only once column that needs to be exposed to a spatial join. However to produce a report back the other way, i.e. select a point on the map and reveal the joined attributes, I believe the rdb needs to live in QGIS. I have spent some time setting up in DBeaver and pgsql, but foreign key joins don't get recognised in QGIS.
Any thoughts appreciated