Editing and version control

Step one: create a table of "edits". One edit can be used for more than one change. For example, adding a child to a family will need to update both an INDI record and a FAM record.

CREATE TABLE edit (
  edit_id INTEGER AUTO_INCREMENT NOT NULL,
 /* other columns for user_id, timestamp, etc. */
 PRIMARY KEY edit_id
);

Step two: each of the "genealogical data" tables (possibly just the "record" table?) gets two new columns: created_by and deleted_by. For example

CREATE TABLE record (
  record_id INTEGER AUTO_INCREMENT NOT NULL,
  xref VARCHAR(20) NOT NULL,
 data TEXT NOT NULL,
 created_by INTEGER NULL,
 deleted_by INTEGER NULL,
 PRIMARY KEY (record_id),
 UNIQUE KEY ux1 (xref, created_by),
 UNIQUE KEY ux2 (xref, deleted_by),
 FOREIGN KEY fk1 (created_by) REFERENCES edit (edit_id),
  FOREIGN KEY fk2 (deleted_by) REFERENCES edit (edit_id)
);

Suppose this table initially contains two records.

+-----------+------+--------------------------------+------------+------------+
| record_id | xref + data | created_by | deleted_by |
+-----------+------+--------------------------------+------------+------------+
+ 1 | I001 + This is record one | NULL | NULL |
+ 2 | I002 + This is record two | NULL | NULL |
+-----------+------+--------------------------------+------------+------------+

If we edit record I002, we would have this:

+-----------+------+--------------------------------+------------+------------+
| record_id | xref + data | created_by | deleted_by |
+-----------+------+--------------------------------+------------+------------+
+ 1 | I001 + This is record one | NULL | NULL |
+ 2 | I002 + This is record two | NULL | 1 |
+ 3 | I002 + This is edited record two | 1 | NULL |
+-----------+------+--------------------------------+------------+------------+

If we edit record I002 again, we would have this:

+-----------+------+--------------------------------+------------+------------+
| record_id | xref + data | created_by | deleted_by |
+-----------+------+--------------------------------+------------+------------+
+ 1 | I001 + This is record one | NULL | NULL |
+ 2 | I002 + This is record two | NULL | 1 |
+ 3 | I002 + This is edited record two | 1 | 2 |
+ 4 | I002 + More edits to record two | 2 | NULL |
+-----------+------+--------------------------------+------------+------------+

If we delete record I001, we would have this:

+-----------+------+--------------------------------+------------+------------+
| record_id | xref + data | created_by | deleted_by |
+-----------+------+--------------------------------+------------+------------+
+ 1 | I001 + This is record one | NULL | 3 |
+ 2 | I002 + This is record two | NULL | 1 |
+ 3 | I002 + This is edited record two | 1 | 2 |
+ 4 | I002 + More edits to record two | 2 | NULL |
+-----------+------+--------------------------------+------------+------------+

If we add record I003, we would have this:

+-----------+------+--------------------------------+------------+------------+
| record_id | xref + data | created_by | deleted_by |
+-----------+------+--------------------------------+------------+------------+
+ 1 | I001 + This is record one | NULL | 3 |
+ 2 | I002 + This is record two | NULL | 1 |
+ 3 | I002 + This is edited record two | 1 | 2 |
+ 4 | I002 + More edits to record two | 2 | NULL |
+ 5 | I003 + This is a new record three | 4 | NULL |
+-----------+------+--------------------------------+------------+------------+

A visitor to the site would see accepted versions only:
SELECT * FROM record WHERE created_by IS NULL;

A logged in user would see the latest version only:
SELECT * FROM record WHERE deleted_by IS NULL;

On the edit-record page, where we need to show red/blue boxes, we would want to fetch original and latest versions (but not the intermediate version):
SELECT * FROM record WHERE xref='I002' AND (created_by IS NULL OR deleted_by IS NULL);

Accepting changes need to happen on all genealogical tables at the same time (in a database transaction).

To accept change #N, we require the following
DELETE FROM record WHERE deleted_by=#N;
UPDATE record SET created_by=NULL WHERE created_by=#N;

To reject change #N, we require the following
DELETE FROM record WHERE created_by=#N;
UPDATE record SET deleted_by=NULL WHERE deleted_by=#N;

Note that if we can only accept the first change to a record, and only reject the last change to a record (same as at present). If we try to accept or reject a record out of sequence, we will get a unique-key violation. So, if a user tries to accept/reject out of sequence, the transaction will fail, and we can roll-back.

Thus we can accept changes 1+3 and we can reject changes 2+4.