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

CodePudding user response：

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