Add uniqueness constraint on submissions about central-backend HOT 10 CLOSED

This is semantically true, but will it actually be true in the database? When we add editable submissions, will the instanceId of the edit be different, or will we have some sort of version increment? If the former, how do we relate those records together?

Or will we edit in place? (I am queasy about that.)

from central-backend.

Your notes sound like they intersect with stories 5.07a (update a submission via web interface) and 5.23 (record submission action audit log). I'm not totally sure how we'll store that log or whether we'd store each submission update as a separate version of the submission. (For example, one way we could end up going is to store the latest version of the submission in the submissions table, then the log of changes elsewhere. I think this is how the paper_trail Ruby gem implements it.) With that in mind, since submission edits are down the road (they aren't a "must"), I think we need a solution for now so that duplicate submissions aren't saved to the database. One option might be to add a simple database constraint for now, then rethink it once we get to those two stories.

from central-backend.

Database constraints are extraordinarily hard to walk back. We can't punt this solution.

from central-backend.

What makes a database constraint hard to walk back? I'd definitely also be happy with a solution other than a database constraint. Would you recommend checking for duplicates in the JavaScript instead? Or is there a better third solution?

from central-backend.

No, constraints should always be enforced by the database. But you should set them up correctly from the beginning, or else you're going to have a zoo of different conflicting user databases which may or may not conform with the new constraint you wish to inject.

from central-backend.

It sounds to me like three different implementations of 5.07a/5.23 are currently on the table, and at least so far, all of them seem consistent with adding a constraint on formId, instanceId for now:

1. The first option is that submission updates are stored as new rows of the submissions table with a new instance ID and maybe a link to the previous version of the submission. formId, instanceId would uniquely identify rows in this case.
2. The second option is that submission updates are stored as new rows of the submissions table with the same instance ID but with an incremental version (say this is stored on a version column). formId, instanceId, version would uniquely identify rows. This would be consistent with adding a constraint on formId, instanceId now, because when we'd add submission versions for the first time, all existing rows would have version = 1.
3. The third option is that submission updates update existing rows of the submissions table in place, but a log of the changes are stored elsewhere (this is what the paper_trail Ruby gem does). formId, instanceId would uniquely identify rows.

I'm thinking that if we add a constraint on formId, instanceId now, it would protect against duplicates, and it seems like it's consistent with the implementations we're currently considering for 5.07a/5.23.

Do you think there's a better solution for v1 to prevent duplicate submissions? Or do you think we should move up the timeline for 5.07a/5.23, make those two "musts," and decide how to implement them now?

from central-backend.

@clint-tseng I agree it's really hard/impossible/a bad idea to ADD constraints but I think it's harmless to remove them?

This is not necessarily related to the DB structure but just so you know, ODK XForms has a mechanism for updating instances and it is most similar to @matthew-white's 2 above. Search for deprecatedID here.

from central-backend.

from central-backend.

I would lean toward punting on 5.07a/5.23 and for now adding a constraint on formId, submissionId, if folks are comfortable with that.

from central-backend.

I will note that is possible and often necessary to consider future tasks without upgrading their priority or working on them immediately. I am queasy about ignoring this and applying the constraint but cannot identify the exact problem, so I'll just apply the constraint.

from central-backend.