Comment strategy needed

Question

I'm working on new web application contains such as library

books
pictures
files

every kind of the past kinds of sections has different properties in database and i cant store them information inside one data table, so i need to create 3 different tables.

Visitors can comment on books, files and pictures and i want to develop one module for comment and store all comments inside one table, lets call it (comments)

my question is, what the strategy i have to follow to make this done?

I am thinking about create reference column [reference_id] [nvarchar 50] and i will store the comments like this

files_{id of file}
pictures_{id of picture} and so on... is that would be great method??

thanks

See http://stackoverflow.com/questions/987654/in-a-stackoverflow-clone-what-relationship-should-a-comments-table-have-to-ques/987709#987709 — Bill Karwin, Jan 03 '13 at 00:10
is that's mean to create one to one relation with item_id and dataItem_id as example ? create table for comments comment_id, item_id — Nour Berro, Jan 03 '13 at 00:12

score 1 · Answer 1 · answered Jan 03 '13 at 00:07

You should use separate ItemId and ItemType.

Additionally you can create table with ItemTypes and store ItemId and ItemTypeId.

Structure like this: pictures_{id of picture} will waste a lot of space and will not help in performance or later code development.

Example: how you cut item type from something like this:

picture_1234

You have to search for "_", convert truncated text to number, and write a lot of SQL code...

score 0 · Accepted Answer · edited May 23 '17 at 12:18

I answered a very similar question: In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?

In your case, I would recommend creating a single table Commentables:

CREATE TABLE Commentables (
  item_id INT AUTO_INCREMENT PRIMARY KEY
  item_type CHAR(1) NOT NULL,
  UNIQUE KEY (item_id, item_type)
);

Then each of Books, Pictures, Files has a 1:1 relationship to Commentables.

CREATE TABLE Books (
  book_id INT PRIMARY KEY, -- but not auto-increment
  item_type CHAR(1) NOT NULL DEFAULT 'B',
  FOREIGN KEY (book_id, item_type) REFERENCES Commentables(item_id, item_type)
);

Do the same for Pictures and Files. The item_type should always be 'B' for books, always 'P' for pictures, always 'F' for files. Therefore you can't have a book and a picture reference the same row in Commentables.

Then your comments can reference one table, Commentables:

CREATE TABLE Comments (
  comment_id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  FOREIGN KEY (item_id) REFERENCES Commentables (item_id)
);

Comment strategy needed

2 Answers2