So I've been back and forth between NoSQL databases and relational ones, and I can't solve some problems that are solvable pretty easily in NoSql databases. So here's the issue that I'm struggling with right now:
We have an article table with articles' information in it, and a user will submit an article to the system. Each user can submit many articles but each article is written by only one user, so we have a one-to-many relationship here. Now there should be favorites list for users' favorite articles, meaning that any user can have many articles as his/her favorite ones, and each article can be favorited (if that's a verb) by many users. Hence we have a many-to-many relationship and we create a new table called favorites with user_id and article_id in it as columns. Here's a summary of my current design of the tables which have a circular relationship currently.
Users:
id | name
----------
Articles:
id | title | text | user_id | //here user_id is the writer of the article
-----------------------------
Favorites:
id | user_id | article_id | //here user_id the id of the user
--------------------------- //who favorited the article that article_id
//that article_id points to
So what I want to query now is a result table, that will give me a user's favorite articles' information about the article itself, its writer information AND information of the user to whom this favorite item belong. Something like this:
userName | favoriteArticleTitle | favoriteArticleText | favoriteArticleWriter
-----------------------------------------------------------------------------
Because the user in the first column and the user from the last column come from the same table, I cannot think of a way to join these three tables and get the user.name column without causing an ambiguity.
So my question is how can I achieve a result like I want (if it is even possible)?l