I have a web app with PostGIS point table as its main back-end data. Through this app, points can be :
- inserted (by clicking map AND filling form)
- updated (by moving the point location AND/OR by filling form)
- shown by LeafLet.JS
Currently there's a need to alter the point table schema from the existing ('old') schema to the 'new' schema. The main motive is that I am gonna introduce several new mandatory fields. Accordingly the web app needs to be changed to cover this.
Upon the new release of the app:
- New data goes in through a new form that complies to the new schema
- Updating old data also through the new form (that complies to the new schema)
- Hence the old form is no more
- Both 'old' and 'new' points got rendered to the same Leaflet.js map (with ST_Intersection operator)
There's 2 option on how the change is implemented in DB:
A) Keep both 'schema' in the same table
B) Store them in different table
A) Keep both 'schema' in the same table, only distinguished by a flag (field) with 2 possible values. Rows existed before the change have all new fields NULL. When old rows updated they gonna get their flag switched from 'old' to 'new'. New rows will always get flag that represent 'new'.
B) Store them in different table. Here there's no need to have flagging field. The 'old' table will have zero-growth. When updated, rows from 'old' table got moved to the 'new' table. New data goes to 'new' table.
Plan B is preferable for its clear distinction between the 2 schema.
Plan A is -i think- more friendly for the ST_Intersection (as i think that the Intersecting Leaflet BBOX against 2 table costs alot).
- Do I put all consideration correct?
- Which one is better: A vs B ?