DbSchema | MongoDB Virtual Foreign Keys

MongoDB is a popular database that doesn’t use any schema. It stores data in JSON-like files and does not impose any structure. Compared with the traditional RDBMS, MongoDB requires a different way of approaching the database design. Overall, the database is easier to scale because it doesn’t impose many restrictions.

What is a foreign key?

A foreign key is a column or a group of columns that acts as a cross-reference between two tables. It consists of a primary key column on the parent table, referenced by other columns from the child tables.

For example, we can take two tables, country, and city. We want to reference these two tables so that every city will be associated with the right country. This can be done via a foreign key:

In the example above, if we read the data from the city table, we’ll see that every city has the associated country_id. This will make it very easy for us to search for all the cities in the USA from a single query.

When setting up foreign keys, we can choose what actions to take when data is updated or deleted. For example, we can choose to cascade, meaning that if a country is deleted, then all the cities associated with it will be deleted. We can also choose to set null, meaning that when data is deleted in the country table, the cities associated with it will be set to null.

MongoDB References

Normally, MongoDB doesn’t work with the concept of foreign keys. The foreign keys can be used in the Relational Databases for visualizing data from multiple collections simultaneously.

The way MongoDB stores data is also different. Instead of using tables, columns, and rows, MongoDB uses collections, objects, and fields.

MongoDB offers two ways of storing data in collections:

1.Denormalization - different data is embedded in the same collection. The example below shows how the __persons collection has embedded the __addresses associated with each person.

> db.person.findOne()
{
  name: 'Mark Kornfield',
  ssn: '1223-234-75554',
  addresses : [
     { street: '123 Church St', city: 'Miami', cc: 'USA' },
     { street: '123 Mary Av', city: 'Los Angeles', cc: 'USA' }
  ]
}

2.Normalization is when different data is stored in different collections. The process is similar to how we store data in Relational Databases, but instead of tables, we use collections.

In MongoDB, the references are mostly used for the normalization process. The references from the children table (usually an ObjectID) are then embedded in the parent table. When reading the information, the user has to perform several queries in order to retrieve data from multiple collections.

For example, we will take the next two collections, parts and products. A product will contain many parts that will be referenced in the product collection.

> db.parts.findOne()
{
    _id : ObjectID('AAAA'),
    partno : '1224-dsdf-2215',
    name : 'bearing',
    price: 2.63

> db.products.findOne()
{
    name : 'wheel',
    manufacturer : 'Ford',
    catalog_number: 2234,
    parts : [     // array of references to Part documents
        ObjectID('AAAA'),    // reference to the bearing above
        ObjectID('F17C'),    // reference to a different Part
        ObjectID('D2AA'),
        // etc
    ]
}

To receive the parts for a particular product, we will first have to fetch the product document identified by the catalog number:

> product = db.products.findOne({catalog_number: 2234});

Then, fetch all the parts that are linked to this product:

> product_parts = db.parts.find({_id: { $in : product.parts } } ).toArray() ;

Creating Virtual Foreign Keys in MongoDB

DbSchema stores the local image of the schema in the model file. This means that you can work offline on the project, without being connected to the database. After reconnecting, you can compare & synchronize the differences between the database and your local project file. Working with a project file enables you to create virtual foreign key relationships. These foreign keys can be used only in DbSchema and won’t affect the database in any way.

The foreign keys can be created without effort. Let’s take two collections, __cities and __countries. Both will have a common field “country_id” for referencing.

To create a foreign key, just drag & drop one column over the other. This will open a new dialog where you can add a description to the foreign key or add more fields to it.

Faster Data Browsing

When using the virtual foreign key, you can browse the data from your collections easier. Using the Relational Data Editor, you will be able to browse data from multiple collections simultaneously.
Based on the foreign key, you can browse the cities associated with the countries in no-time.

Conclusion

With the help of DbSchema, you can visualize your MongoDB collection as diagrams and create virtual foreign keys. This will enable you to simultaneously browse data from multiple collections at the same time. The virtual foreign keys will be saved only to the local project file and won’t affect the database in any way.