December 8th, 2012, 01:59 AM
Multiple many-many relations between two mysql tables
I have two Main tables, Post and User, I am facing issue of multiple relations as per requirement given bellow:
A Post Belongs to a User.
A Post may be addressed to one or Many Users
Users can follow a Post to receive notification when there is some reply/comment on a post.
A User can add a Post to his ToDo list and when he completes this ToDo he can mark this ToDo Done (so this ToDo be removed from his ToDo list)
Users Should get notification when a post is addressed to a User, or when some reply/comment is given on Post the User is Following. (A user who create the Post , or A user who is addressed in Post, will be automatically following that Post).
So This way i need to create different many many relations ships between Post and User tables, so do i Need to create a different joining table for each relation? like Post_user_follower(id,PostId,UserId) to save the data of Users who are following some Post, and so on for each relation, is this a normal case or there will be circular joins creating infinite loops?
can i save additional data in the joins tables along with the Id's of joining tables e.g for ToDO's, i will create a table Pots_user_toDo (id,userId,PostId,toDo_status) so that i can save the status of ToDO in the join table to check whether the ToDO is pending or completed, or i need to save the ToDo_status in another tables e.g ToDo(id,toDO_status)
December 10th, 2012, 02:24 AM