1

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)
  • deleted
  • 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

In details:

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).

Now:

  1. Do I put all consideration correct?
  2. Which one is better: A vs B ?

closed as primarily opinion-based by ThingumaBob, Fran Raga, Vince, HDunn, Taras Jun 30 at 20:33

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

1

I would definitely go with option A. Having two tables for the same object (in different states) just doesn't feel right to me.

There are chances that you won't be needing a 'flag' column at all. Check your old vs new 'schema' and try to identify columns which are NOT NULL as indicators of whether a point lies in the old or new.

For instance, if in the old schema was a column that its completion was mandatory (NOT NULL) and this column is not used in the new schema or if in the new schema there is a mandatory column (NOT NULL) that was not used in the old schema. By checking this (and updating it) you always have a clear distinction as to what "schema" a particular point lies.

  • To be honest, when considering plan A, me and my colleague do strongly, strongly believe to have distinguishing flagging field. Now its interesting to have your opinion against it. We definitely gonna consider it. But for a query that needs to get rows of 'old' shcema (the app will need this query) don't you think that it'll be cheaper to have this flag afterall rather than relying on those null check? Also, when there's mandatory field from 'old' schema but unused in 'new' schema, how do i put this as constrain for the table? This is exactly my reason for having plan B. – Rino Jun 30 at 15:03
  • Of course you can add a flag and be done with it. I just mentioned it. By old and new schema I understand that there are some columns in the table that are exclusively used for old "schema" and some rows that were added for the new schema. If for the "new" schema there is a column that is always populated by the form (it is mandatory) then as you said, the old schema rows will have NULL in this column. By checking the value of the column you can determine if the point belongs to the old or new. I understand that if an old point gets updated the this column will not be NULL anymore. – vagvaf Jun 30 at 16:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.