DbSchema Database Designer

DbSchema | MongoDB Virtual Foreign Keys

Publish on DbSchema Blog >>>

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:

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
        // 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.

drag & drop

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.


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.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.