Alex's blog

A crime against database design

Published on 2022-07-08

I present, a crime against database design, in interface definition form.


Inputs consist of one or more rows inserted into a table resembling the following:

CREATE TABLE input_data (
  seq INTEGER PRIMARY KEY AUTOINCREMENT
);

The input_data table must have that name exactly, but may (and should) have following columns to contain the data.

Once rows are inserted into input_data, the database should generate output accessible via a table or view resembling the following:

CREATE TABLE output_data (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  done INTEGER CHECK(done IN (0, 1))
);

Again, output_data must have that name exactly and may have following columns, comprising the output.

After inserting each input row, output_data should be checked for new rows with a greater seq value than previously seen; these are the output rows. The database is not required to preserve old output rows when new input is inserted; however, seq must continue to increase throughout the process. Once all input data has been inserted, if the second column (done) of the last output row is 0, rows of all NULL should continue to be inserted into input_data until a row with done 1 appears at the end of output_data.

input_data should be cleared (truncated/deleted from unconditionally) before any new inputs. Once this occurs, the database is permitted to reset or rewind the seq values used for the next output, and the same is true of the next input.

Tagged: Personal, Databases, Evil