[FIXED] Building a table of "Likes for feedbacks", SQL

Issue

Im building a new table related to my feedback feature. My backend build with Java, spring boot, Im using hibernate. The question is if to insert column of ID or WITHOUT? Because I have an ID and hibernate knows what he is looking..

CREATE TABLE feedback_helpful (
user_id BIGINT NOT NULL,
comment_id BIGINT NOT NULL,
timestamp TIMESTAMP DEFAULT NOW(),
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(comment_id) REFERENCES feedback_comment_public(id),
PRIMARY KEY(user_id, comment_id)
);

The question is if will it afect on the speed? The binding models from the DB will be faster? What is the best way to build it?

Thanks

Solution

Case 1: Many users upvoting many comments -- a classic "many-to-many" relationship. There is one best way

-- no id
PRIMARY KEY(comment_id, user_who_upvoted)
INDEX(user_who_upvoted, comment_id)

One index makes one direction efficient; the other takes care of the other direction.

Case 2: (This is where the "user_id" wrote the "comment".)

Since you have a "natural" PRIMARY KEY(user_id, comment_id), there is no need to add an artificial ID.

Although this looks like a "many-to-many" mapping table, I don't think it really is. I expect that one 'user' can have many 'comments'. But can one 'comment' have many 'users'?

If not, then it is really a "1-to-many" relationship. Such a relationship should not be implemented with an extra table. Instead

• users has no mention of the comment

• comments -- each row has the user_id that it relates to.

• comments probably needs PRIMARY KEY(comment_id), INDEX(user_id).

• comment_id is AUTO_INCREMENT` and is probably used only for providing a unique key for that table. (It is unlikely to have a 'natural' PK.)

• If you are frequently fetching all the comments for one user, then this is slightly better than the indexing suggestion above:

  PRIMARY KEY(user_id, comment_id),  -- for efficient access
  INDEX(comment_id)  -- to keep auto_increment happy
  

Answered By - Rick James
Answer Checked By - Timothy Miller (JavaFixing Admin)