Building comment system for different types of entities

Question

I'm building a comment system in PostgreSQL where I can comment (as well as "liking" them) on different entities that I already have (such as products, articles, photos, and so on). For the moment, I came up with this:

(note: the foreign key between comment_board and product/article/photo is very loose here. ref_id is just storing the id, which is used in conjunction with the comment_board_type to determine which table it is)

Obviously, this doesn't seem like good data integrity. What can I do to give it better integrity? Also, I know every product/article/photo will need a comment_board. Could that mean I implement a comment_board_id to each product/article/photo entity such as this?:

I do recognize this SO solution, but it made me second-guess supertypes and the complexities of it: Database design - articles, blog posts, photos, stories

Any guidance is appreciated!

You might find inheritance useful for this purpose (http://www.postgresql.org/docs/current/static/ddl-inherit.html). — Gordon Linoff, Feb 27 '16 at 02:26
@GordonLinoff Were you thinking products/articles/photos would inherit comment_board? It seems like one of the issues with postgres inheritance is that it can't do index/FK-constraints to the children. maybe not a big deal. I am considering changing comment_board to be "entity", and then ending up with: `Entity (entity_id PK, created_at, updated_at)` and `product (id, created_at, updated_at, entity_id)`, and `comment(id PK, entity_id REFERENCES entity(entity_id), ...)`. The only thing I don't know how to do is inherit other tables after the fact — Handonam, Feb 27 '16 at 23:56

score 0 · Accepted Answer · answered Feb 28 '16 at 01:21

I ended up just pointing the comments directly to the product/photo/article fields. Here is what i came up with in total

CREATE TABLE comment (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now()),
  updated_at TIMESTAMP WITH TIME ZONE,
  account_id INT NOT NULL REFERENCES account(id),
  text VARCHAR NOT NULL,

  -- commentable sections
  product_id INT REFERENCES product(id),
  photo_id INT REFERENCES photo(id),
  article_id INT REFERENCES article(id),

  -- constraint to make sure this comment appears in only one place
  CONSTRAINT comment_entity_check CHECK(
    (product_id IS NOT NULL)::INT
    +
    (photo_id IS NOT NULL)::INT
    +
    (article_id IS NOT NULL)::INT
    = 1
   )
);

CREATE TABLE comment_likes (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT (now()),
  updated_at TIMESTAMP WITH TIME ZONE,
  account_id INT NOT NULL REFERENCES account(id),
  comment_id INT NOT NULL REFERENCES comment(id),

  -- comments can only be liked once by an account.
  UNIQUE(account_id, comment_id)
);

Resulting in:

This makes it so that I have to do one less join to an intermediary table. Also, it lets me add a field and update the constraints easily.

Building comment system for different types of entities

1 Answers1