I m creating an library for posts(just like facebook posts) in my application, each post can have multiple tags associated with it, and users can also see posts by tags, so i have 2 tables :-
"posts_has_tags"
post_id -> tag_id
"tag_has_posts"
tag_id -> post_id
a developer can can query for an post and tag using library like this ->
Post $post = new Post($postId)
Tag $tag = new Tag($tagId)
then i want to add functions that will attach tags to post, but now i m confused, because i can create 2 seperate functions, one which add tags to post (in post class), and another that add post to tag (in tag class) ex:- first :-
$post->addTags([$tag1, $tag2])
then
for (i = 0; i < numOfTags; i++){
$tag{i}->addPost($post)
}
so here developers(library user) will have 2 functions, they will have to manually add tags to post, and also have to add that post to tag, so if second operation fails(if these tables are on seperate machines), they can rollback first operation,
or
i can just provide a single function in Post class that will do all stuff automatically, ex:-
$post->addTags([$tag1, $tag2...])
so which approach is better ?, assume that my database is sharded by function(mean post_has_tags and tag_has_posts tables can be on different machine), so by first approach a library user can create distributed transaction at application level, if second machine fails, we can rollback first operation by removeTags() function, and in second case this should will done at library level,
i want to know which one is better ?, same apply for user followers, where you can see followers and followings, and those things can also be on different machines, sorry for weak english ,thanks :)
This is more commonly done with a single table, one which would have something like ...
table: postAssciatedTags
columns: postID,
tagID,
taggedDate (optional)
with a primary key of postID and tagID (as you can only have a post tagged once with the same tag).
So you only ever add 1 record which is bi-directional, you can see which tags a post has or which posts are tagged with a certain tag.