creating one tree to many observations in qgis

Question

Looking to create a database for QGIS where each point represents a tree (with name attribute) and another table of observations about each tree over time. Ideally I can update tree location in QGIS without breaking the relationship (sometimes they are inaccurate).

Answer 1

A simple way to go at this would be to add a time column to your observations. That way, a tree can have many rows of observations ( so long as the time and tree combinations are unique ).

Answer 2

Additionally, you might consider using a foreign key constraint on your observations table so that each observation correctly references an existing tree. For example in spatialite:

CREATE TABLE trees (tree_id integer primary key autoincrement, 
   tree_name text, 
   ... <other attrib columns>);
SELECT AddGeometryColumn('trees','Geometry',<your SRID>,'POINT,'XY');
CREATE TABLE observations (observe_id integer primary key autoincrement,
   tree_id INTEGER REFERENCES trees(tree_id),
   observe_date date,
   observe_data1 integer, observe_data2 text, ...<more observation data>);

With a structure like this,

• You can update the trees table (correct locations, or add new trees) and the link between the trees and observations will remain.
• Further, you will be able to add observations only for trees that actually exist in the trees table.
• And, when it comes time to aggregate some observation data over time for each tree, you're all set, using a JOIN between the tables on the tree_id columns.