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.
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.