How to enforce foreign keys in NoSql databases (Mo

Question

Let's say I have a collection of documents such as:

{ "_id" : 0 , "owner":0 "name":"Doc1"},{ "_id" : 1 , "owner":1, "name":"Doc1"}, etc

And, on the other hand the owners are represented as a separate collection:

{ "_id" : 0 , "username":"John"}, { "_id" : 1 , "username":"Sam"}

How can I make sure that, when I insert a document it references the user in a correct way. In old-school RDBMS this could easily be done using a Foreign Key.

I know that I can check the correctness of insertion from my business code, BUT what if an attacker tampers with my request to the server and puts "owner" : 100, and Mongo doesn't throw any exception back.

I would like to know how this situation should be handled in a real-word application.

Thank you in advance!

Answer 1

As a NoSQL solution, you can use MariaDB (NewSQL) Its a combination of SQL and NoSQL and implemented by MySQL developers with a great performance.

Answer 2

To answer your specific question - while MongoDB encourages handling foreign-key relationships on the client side, they also provide the idea of "Database References" - See this help page.

That said, I don't recommend using a DBRef. Either let your client code manage the associations or (better yet) link the documents together from the start. You may want to consider embedding the owner's "documents" inside the owner object itself. Assemble your documents to match your usage patterns and MongoDB will shine.

Answer 3

This is a one-to-one to relationship. It's better to embed one document in another, instead of maintaining separate collections. Check here on how to model them in mongodb and their advantages.

Although its not explicitly mentioned in the docs, embedding gives you the same effect as foreign key constraints. Just want to make this idea clear. When you have two collections like that:

C1:

{ "_id" : 0 , "owner":0 "name":"Doc1"},{ "_id" : 1 , "owner":1, "name":"Doc1"}, etc

C2:

{ "_id" : 0 , "username":"John"}, { "_id" : 1 , "username":"Sam"}

And if you were to declare foreign key constraint on C2._id to reference C1._id (assuming MongoDB allows it), it would mean that you cannot insert a document into C2 where C2._id is non-existent in C1. Compare this with an embedded document:

{
    "_id" : 0 , 
    "owner" : 0,
    "name" : "Doc1",
    "owner_details" : {
        "username" : "John"
    }
}

Now the owner_details field represents the data from the C2 collection, and the remaining fields represent the data from C1. You can't add an owner_details field to a non-existent document. You're essentially achieving the same effect.

Answer 4

I would also reccommend that if username's are unique, then use them as the _id. You will save on an index. In the document being stored, set the value of 'owner' in the application as the value of 'username' when the document is created and never let any other piece of code update it.

If there are requirements to change the owner, then provide appropirate API's with business rules implemented.

There woudln't be any need of foreign keys.

Answer 5

If someone really wants to enforce the Foreign keys in the Project/WebApp. Then you should with a MixSQL approach i.e. SQL + NoSQL

I would prefer that the Bulky data which doesn't have that much references then it can be stored in NoSQL database Store. Like : Hotels or Places type of data.

But if there is some serious things like OAuth modules Tables, TokenStore and UserDetails and UserRole (Mapping Table) etc.... then you can go with SQL.

Answer 6

MongoDB doesn't have foreign keys (as you have presumably noticed). Fundamentally the answer is therefore, "Don't let users tamper with the requests. Only let the application insert data that follows your referential integrity rules."

MongoDB is great in lots of ways... but if you find that you need foreign keys, then it's probably not the correct solution to your problem.