CODEWITHSUNDEEP
mysqldatabase
user1724416
user1724416

Reputation: 954

Comment table mySQL

I want to create a 'comments' table, so holds text, created, userId etc...

But a user can comment on many things.

So I cant put a foreign key to the 'Post' table as I also need to be able to comment on the 'Picture' table, what is the best practice for this?

So basically a multiple table link table?

Upvotes: 1

Views: 92

Answers (1)

MikeVe
MikeVe

Reputation: 1120

Comment table:

comment(comment_id,text, created, userid, other_field,...)

Link table

link(comment_id,comment_type, refID, )

where comment_type would be the name of the table you commented on ("picture", "post",...). comment_id would be the id of the comment a user made. refID is the id of the table the user commented on (Post_id, Picture_id,...)

Lets say you have these table entries:

POST(id, some_text,other_stuff,...):
(1, "Hello Hello", "something",...)
(2, "Hey there", "something_else",...)
...
(57, "TEST TEST", "another something",...)

PICTURE(pic_id, description, other_stuff):
(4, "A cat", "something",...)
(2, "Another cat", "something_else",...)
...
(57, "finally a dog", "another something",...)

When a user ads a new comment:

Comment

(1,"What a cute cat", 2015-10-02, user24)
(3,"That something is awesome", 2015-10-02, user87)

link

(1,"Picture", 4) //That comment is about the cat (Picture ID 4)
(3, "Post",57) //That comment is about the post with ID 57

Upvotes: 1

Related Questions