I understand the difference between NoSQL and SQL, but I still have a small question. It's about a many-to-many relationship. I know that if I need such a relationship, I should use relational databases. However, in my case, I prefer to use document-oriented databases, because I need to store a large number of documents, much larger than the number of entities with relationships.
So, I need to implement user groups. Of course, users can exist outside of groups, therefore they are documents of a separate collection. In addition, one user can be in several groups, which means this is a real many-to-many relationship. People say that “mongo-way” is to make a user with a list of links to groups and a group with a list of links to users, but this option doesn’t suit me, because sometimes I need to display a list of groups without displaying a list of the group users, which can take most of the document.
As an alternative, I want to use the traditional “relationship tables” that are used for many-to-many relationships in relational databases.
So my question is, what is the practical difference between using such tables in mysql or in mongodb? As far as I know, there are no foreign keys in mongodb, but does that really prevent me from doing something like this? I see the problem only in the fact that you can not get rid of the required _id and its indexes. By the way, in this case, should I create indexes for the UserId and GroupId fields?
Or maybe I should give up the idea to fit everything into one database and use SQL and NoSQL together in one project?
sometimes need to display a list of groups without displaying a list of the group users. For example, I just want to list the names of groups, but in addition I get unnecessary links to all its users, which is ten times more than the information I need. - Exeteres