Migrate SQL Server DB to MongoDB

Question

I want to Migrate all the features and functions of SQL Server DB to Mongo DB, But I am stuck with Foreign Key Concept, I know there is no Concept ForeignKey in NoSQL Dbs, But I want to connect and Store the Tables as Shown Below, Please share your thoughts and Ideas how to achieve this. I have three Tables named AR_Cities, AR_Flights and AR_FlightAvail in SQL Server I need to migrate and store to MongoDB, How do I store these Datas with Foreign Key Concept/ Normalized Table structure.

Please suggest your Ideas ,

Image URL:- https://i.stack.imgur.com/nezP0.png

Answer 1

The most efficient way of storing this is as two separate tables, it would be a bad idea to nest the city information into the flight, that could cause massively duplicated information, creating a hairball of problems for updating etc.

Unlike in the linked question by @Maksym I would NOT say normalisation is discouraged instead that embedding of self containing parent data is encouraged, such as many-many relationship ids.

As an example of a good way to store relationships is by using ObjectIds only instead of the more formed version of DBRefs. So, as an example, a flights which visits 5 cities en route would look like:

{
    _id: ObjectId,
    cities: [
        ObjectId(),
        ObjectId(),
        ObjectId(),
        ObjectId(),
        ObjectId()
    ]
}

Of course your document may look a little more complex because you probably need estimated landing times too and status (delayed/cancelled etc), however, this shows the basic concept. In your application you will take the _ids of the city documents and add then to the flight record. This way you can get all cities for that flight like:

var d=db.flights.findOne({_id:ObjectId()});
var cities=db.cities.find({_id:{$in:d.cities}});

That will give you your relation, in a very basic sense.

As for relation holding, there is none in MongoDB itself. DBRef is not a server-side resolved relation. This is something you will have to handle manually within your application however, I do not think that you will need cascading deletes etc in this system, flights are stored forever and city data is reused many times over.

As such this system doesn't really need anything more than a lookup JOIN which is easy enough in the application itself using either a lazy load of each related record of doing a ranged $in query for all associated city/flight information.