[FIXED] How to build a relationship between a 'user' and 'his comment'?

Issue

I'm currently coding a blog to get experience with php(I've made an MVC-Framework), so I am still new to this.

I have two tables important for this question:

user(id, username, password, registrated)

comments(id, content, post_id, comment_author, date, editedAt, editedBy)

In the comments-table comment_author is yet not linked to the id of the user, because I was unsure how to actually do this.

A user can write as many comments as he likes, but a comment can only have one author.

comment_author has the username in it at the moment, but I know I need the id(if the user gets deleted and someone else would registrate with this username, the comment would be his).

How should I now structure the tables?

1.) comments_author_id in comments-table, id in user as foreign key:

In this case I would have the id of the Comment author in the comments-table, but the user would not know about the comments he has written. If I want to show the recent comments of the user on it's profile, could I get them with an inner-join query then?

2.) make a new table user_comments(id, user_id, comment_id)

In this case user and comments wouldn't know about it's author/comments.

Also I have 'editedBy' in which the username of the last editing user is. Can I somehow link it with the username in the users-table or should I also link it with the id?

I am really lost with this question since I don't know much about databases; So I appreciate every help and advice I can get.

Please also let me know if I need to give any further information or change something for a better understanding.

Solution

It make sense that you go with the first option.

As you said comment has only one author so just use a foreignkey to user table.

In the second option you're creating a hole new table for a data that doesn't need a new table so you're storing data's that you don't need. also you have to write in two different tables which is two different operations for adding a comment and it gives you no extra feature that you can use.

Answered By - Navid Zarepak