Objects in two rooms example

Let there be two rooms. Let there be a predicate: 

    p(OBJECT#, ROOM#) = 
        object OBJECT# is in room ROOM#

Let B be a base relvar with attributes {OBJECT#, ROOM#} and there are views on B defined as 

    V1 = RemoveDuplicates(B WHERE (ROOM# = 1), { OBJECT# })
    V2 = RemoveDuplicates(B WHERE (ROOM# = 2) , { OBJECT# })
    V3 = RemoveDuplicates(B, { OBJECT# })

Let a user of the database that is concerned with "what is supposed to be the case" according to the database, with regarded to predicate p(OBJECT#, ROOM#) work on the assumption that the extension of that predicate is given by V3.

Consider a change to the world situation where an object is moving from room 1 into room 2.

If the users update the database without delay then a tuple will be deleted from V1 as the object exits room 1, and later a tuple is inserted into V2 as the object enters room 2.

In that case there are no duplicates in B and so B always equals V3.

Suppose instead that users issue updates with significant delays, and it so happens that the insertion of a tuple into V2 is performed first.

Since B doesn't enforce a key constraint on OBJECT#, and the new tuple has a different ROOM#, a distinct tuple is inserted into B.

The effect however is for a tuple to be deleted from V3 (because V3 strips away duplicates).

It is as though V3 carries an assumption that the world situation corresponds to the case of the object still being in transit between the two rooms (or any other possible explanation).

Clearly this is arbitrary (and not even necessarily wrong - for all we know the object has just exited room 2 and is on its way back again).

The important thing to note is that we already know that the database isn't being kept in sync with the world situation (because of the delays in the updates which were assumed in order for duplicates in B to be seen) so we have no right to claim the approach is illogical according to "what is supposed to be the case".

Keep in mind that it is a given application requirement that a user performing data entry is only authoritative for a single room.

The delays in applying updates are large enough that there are temporary inconsistencies.

This is regarded as an inevitable limitation in the sources of information used to update the database, which is ironical given that the database is supposed to be recording consistent snapshots of the world.

There isn't even an assumption that a user is providing updates for every change that actually occurs in their room (e.g. while the user isn't looking an object exits and reenters the room).

In that case, according to the information available to the database it is never possible to infer that particular world situations actually occur.

So any recorded world situation is an "artifact" - which is nevertheless useful to users not bothered by the fact that it's a simplified approximation of the truth.

Possible solutions:

  1. Require users to be authoritative for both rooms, and to apply updates they really do represent world situations that actually occur. But is this possible?
  2. Require users to make updates with less delay, so that temporary inconsistencies cannot occur. But is this possible?
  3. Have the database reject updates that cause temporary inconsistencies. This might annoy the hell out of them (because they may have to wait until an update succeeds). Ironically this makes it even more likely that the database isn't up to date (and in systems with more onerous integrity constraints, users may have to spend a lot more time preparing complex changes in GUIs (e.g. using dialogs), that only update the database when they click on the "apply" button or whatever. This increases delays and aggravates the problems of asynchronous updates.
  4. Don't put the information from both rooms into the same database, so no-one can write a query that assumes a consistent snapshot.
  5. Allow for inconsistencies, and calculate an "artifact" which is consistent across both rooms (say by removing duplicates, or by giving precedence to one room over the other - perhaps according to which update was performed most recently).

The 5th option seems best in practise.