The Worst Database Design

Let’s create tables… like this:

CREATE TABLE A
(
    ID PRIMARY KEY
    ...
);

CREATE TABLE B
(
    ID PRIMARY KEY
    ...
);

The whole idea is that (due to some reasons that I don’t remember) there is one-to-one relation between those two tables (A.id = B.id). One-to-one relation is not quite normal and should be avoided (avoided means don’t do that unless you know what you’re doing). The first table looked like this:

CREATE TABLE A
(
    ID SERIAL PRIMARY KEY
)

So all values for the id column are taken from a sequence. That’s quite normal. The obvious solution was to insert into the second table the id taken from the first table’s sequence. Making all that in one transaction would look like this:

BEGIN;
    INSERT INTO A(...) VALUES (...);
    INSERT INTO B(id, ...) VALUES (1, ...);
COMMIT;

If the rollback occurs, then there will be a gap in the numeration, so there is not such a big problem. That’s quite normal with sequences as they are not transactional. Not transactional means that after a rollback the number taken from a sequence won’t go back, so the sequence is only incremented.

The problem with the proposed and normally used solution was that the table looked like this:

CREATE TABLE A
(
    ID SERIAL PRIMARY KEY
    ...
);

CREATE TABLE B
(
    ID SERIAL PRIMARY KEY
    ...
);

So here we’ve got two sequences. Two quite different not transactional sequences. Normally application makes queries like:

BEGIN;
    INSERT INTO A(...) VALUES (...);
    INSERT INTO B(...) VALUES (...);
COMMIT;

So A.id is taken from the A-sequence and B.id is taken from the B-sequence. What if the second insert fails? Then the A-sequence has been incremented and the B-sequence not. Let’s look at the sequences state after some inserts:

So after some time we can see that there is one inconsitency in the whole table: the record where A.id=4, all others are OK. That error can be found after years and during that time there can be more and more such mistakes so the data becomes more and more inconsistent and suddenly the whole database becomes full of shit instead of data. Usually there is no way to repair the data after some months of such mistakes. If one doesn’t want to desing a proper database from the very beginning then good luck with improving.

Bookmark It

Related posts:

1. Testing Database – Small Reply On Ovid’s blog I found lately this entry. First of...
2. How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
3. SQLAlchemy Defines New SQL Standard Dreaming About SQL Well, I thought that I know the...
4. PostgreSQL FOUND Problem FOUND is a global variable that exists in the plpgsql...
5. Are SQL Database Joins So Bad? Last time on many forums there were endless discussions about...
6. Are Long Running Transactions Bad? Simple answer on how short a transaction should be and...