I am creating a spatial database using PostGIS.
I am storing the attributes and location of some species records that can be represented as points. The records are recorded from either:
a) static survey position (where previously I have just represented the species point as the static position) or b) a transect walked around the site and a position is marked when a record is recorded.
The database stores the static positions in a a separate table, so for a) above I don't actually have to store a point against each of these records as I can do a join on the species table and the static location table. If I did store the location, if the static location changed, I would have to also make sure I moved the location of all of the species records associated with that point.
The transect results are stored with spatial data as they could be recorded anywhere around the walked route and there is no other way to know where they were recorded.
I would all of this data in the same table as it seems silly to have to maintain two tables for records about the same species (would have to make changes to the table structures in two tables).
I can see 3 ways to deal with this:
1) Store the species data in two separate tables, one spatial and one not. 2) I am guessing you cant have NULL spatial values in a geometry column? 3) Give all the records spatial data, and move the points from the species table when the static table locations change? Can you have a relationship / procedure in the database that automatically does this for you?
Which of these approaches is the most sensible? Or have I missed something. Any thoughts would be much appreciated.