Path dependence in relational design - by Alexey Makhotkin

Here is a very simple table design problem: suppose that we want to build an issue tracker, where people can leave comments and also upvote those comments.

The design is very straightforward (we show only the fields we’re interested in, and a couple of text fields for some realism).

CREATE TABLE issues (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  body TEXT NOT NULL
);

CREATE TABLE comments (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  body TEXT NOT NULL,
  issue_id INTEGER NOT NULL
);

CREATE TABLE comment_upvotes (
   comment_id INTEGER NOT NULL,
   user_id INTEGER NOT NULL,
   PRIMARY KEY (comment_id, user_id)
);

Some time passes, and we decide to add an often-requested feature: stable numbering for comments.  We want to refer to each comment by URLs such as /issue/12345#c1, /issue/12345#c2, etc.  If the comment is removed, we want the numbering for the remaining comments to stay intact.

Leave a Comment