Michael Lynn

Today, we are joined by Julia Oppenheim, Associate Product Manager at MongoDB. Julia chats with us and shares details of a set of features within MongoDB Atlas designed to help developers improve the design of their schemas to avoid common anti-patterns. 

Schema Suggestions with Julia Oppenheim - Podcast Episode 59

Jesse Hall

So your business needs have changed and there’s additional data that needs to be stored within an existing dataset. Fear not! With MongoDB, this is no sweat.

> In this article, I’ll show you how to quickly add and populate additional fields into an existing database collection.

## The Scenario

Let’s say you have a “Netflix” type application and you want to allow users to see which movies they have watched. We’ll use the sample\_mflix database from the [sample datasets](https://docs.atlas.mongodb.com/sample-data/) available in a MongoDB Atlas cluster.

![](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/I_Veb_N_Rm3_Ab3xi_F5c_Phpa3n_3zzf5pvv_I_Fsgnu3_X_6m_U_Knyev_Mrkvy_ZCTUF_5y_LM_Sv_WN_8_E11nbqzv3v_Mo95_C7_WYFGR_3l_OK_4_N2b_Rr9_Z4_Bu_U4_K_Cheipu_V_Rcvcq3_N_Wh_Zd3bu_J_Yafd_V_69c906938e)

Here is the existing schema for the user collection in the sample\_mflix database:

``` js
{
    _id: ObjectId(),
    name: <string>,
    email: <string>,
    password: <string>
}
```

## The Solution

There are a few ways we could go about this. Since MongoDB has a flexible data model, we can just add our new data into existing documents.

In this example, we are going to assume that we know the user ID. We’ll use [`updateOne`](https://docs.mongodb.com/manual/reference/method/db.collection.updateOne/#mongodb-method-db.collection.updateOne) and the [`$addToSet`](https://docs.mongodb.com/manual/reference/operator/update/addToSet/) operator to add our new data.

``` js
const { db } = await connectToDatabase();
const collection = await db.collection(“users”).updateOne(
    { _id: ObjectID(“59b99db9cfa9a34dcd7885bf”) },
    {
        $addToSet: {
            moviesWatched: {
                <movieId>,
                <title>,
                <poster>
            }
        }
    }
);
```

The `$addToSet` operator adds a value to an array avoiding duplicates. If the field referenced is not present in the document, `$addToSet` will create the array field and enter the specified value. If the value is already present in the field, `$addToSet` will do nothing.

Using `$addToSet` will prevent us from duplicating movies when they are watched multiple times.

## The Result

Now, when a user goes to their profile, they will see their watched movies.

![](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/d_I_Tvq_WAL_4_Ap_Z_j_GN_Qx_Vu_Vxw6_3_Dt3_Krn_O_Fl_L9_OI_3be_DD_1_Q58dk_J39f7s0_Vn3p_Ptgcs_P_Ss3_XT_0_B_Kjcqh_H_Erw_J6if2_Ueeb_GA_Gc20_Fmfgt8j9g_H_Tm_Vow_Kk2_Hgb_Kbj_Eg9f_Xg_ACZ_Be_A_bb92f06e85)

But what if the user has not watched any movies? The user will simply not have that field in their document.

I’m using Next.js for this application. I simply need to check to see if a user has watched any movies and display the appropriate information accordingly.

``` js
{ moviesWatched
    ? "Movies I've Watched"
    : "I have not watched any movies yet :("
}
```

![](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/78ye_Oz_Njq_O_Rd2_Ngt_YQ_0ab_X_Fm_Vn_Eriu9_F_Bh_K8_Zx9_FD_Ls_ST_Uk_IR_Ee8_B_Iz_H_Tw_WI_Or_KB_Kzp_Ve_H_Eti_O66_fyb_MA_Eu_Yxa3gf2iq_Lg_Mska_O1w_W_Jc_Gk1_C7_Uk_F_Mud28_Sx_U4yse_0_Tb3_S9_F_e0d789b4bf)

## Conclusion

Because of MongoDB’s flexible data model, we can have multiple schemas in one collection. This allows you to easily update data and fields in existing schemas.

If you would like to learn more about schema validation, take a look at the [Schema Validation](https://docs.mongodb.com/manual/core/schema-validation/) documentation.

I’d love to hear your feedback or questions. Let’s chat in the [MongoDB Community](https://developer.mongodb.com/community/forums/).

So your business needs have changed and there’s additional data that needs to be stored within an existing dataset. Fear not! With MongoDB, this is no sweat. In this article, I’ll show you how to quickly add and populate additional fields into an existing database collection.

Document Enrichment and Schema Updates

John Page

# Paginations 2.0: Why I Would Choose MongoDB


I've been writing and designing large scale, multi-user, applications with database backends since 1995, as lead architect for intelligence management systems, text mining, and analytics platforms, and as a consultant working in retail and investment banking, mobile games, connected-car IoT projects, and country scale document management. It's fair to say I've seen how a lot of applications are put together.

Now it's also reasonable to assume that as I work for MongoDB, I have some bias, but MongoDB isn't my first database, or even my first document database, and so I do have a fairly broad perspective. I'd like to share with you three features of MongoDB that would make it my first choice for almost all large, multi-user database applications.

## The Document Model

The Document model is a fundamental aspect of MongoDB. All databases store records—information about things that have named attributes and values for those attributes. Some attributes might have multiple values. In a tabular database, we break the record into multiple rows with a single scalar value for each attribute and have a way to relate those rows together to access the record.

The difference in a Document database is when we have multiple values for an attribute, we can retain those as part of a single record, storing access and manipulating them together. We can also group attributes together to compare and refer to them as a group. For example, all the parts of an address can be accessed as a single address field or individually.

Why does this matter? Well, being able to store an entire record co-located on disk and in memory has some huge advantages.

By having these larger, atomic objects to work with, there are some oft quoted benefits like making it easier for OO developers and reducing the computational overheads of accessing the whole record, but this misses a third, even more important benefit.

With the correct schema, documents reduce each database write operation to single atomic changes of one piece of data. This has two huge and related benefits.

By only requiring one piece of data to be examined for its current state and changed to a new state at a time, the period of time where the database state is unresolved is reduced to almost nothing. Effectively, there is no interaction between multiple writes to the database and none have to wait for another to complete, at least not beyond a single change to a single document.

If we have to use traditional transactions, whether in an RDBMS or MongoDB, to perform a change then all records concerned remain effectively locked until the transaction is complete. This  greatly widens the window for contention and delay. Using the document model instead, you can remove all contention in your database and achieve far higher 'transactional' throughput in a multi-user system.

The second part of this is that when each write to the database can be treated as an independent operation, it makes it easy to horizontally scale the database to support large workloads as the state of a document on one server has no impact on your ability to change a document on another. Every operation can be parallelised.

Doing this does require you to design your schema correctly, though. Document databases are far from schemaless (a term MongoDB has not used for many years). In truth, it makes schema design even more important than in an RDBMS.

## Highly Available as standard

The second reason I would choose to use MongoDB is that high-availability is at the heart of the database. MongoDB is designed so that a server can be taken offline instantly, at any time and there is no loss of service or data. This is absolutely fundamental to how all of MongoDB is designed. It doesn't rely on specialist hardware, third-party software, or add-ons. It allows for replacement of servers, operating systems, and even database versions invisibly to the end user, and even mostly to the developer. This goes equally for Atlas, where MongoDB can provide a multi-cloud database service at any scale that is resilient to the loss of an entire cloud provider, whether it’s Azure, Google, or Amazon. This level of uptime is unprecedented.

So, if I plan to develop a large, multi-user application I just want to know the database will always be there, zero downtime, zero data loss, end of story. 


## Smart Update Capability

The third reason I would choose MongoDB is possibly the most surprising. Not all document databases are the same, and  allow you to realise all the benefits of a document versus relational model, some are simply JSON stores or Key/Value stores where the value is some form of document.

MongoDB has the powerful, specialised update operators capable of doing more than simply replacing a document or a value in the database. With MongoDB, you can, as part of a single atomic operation, verify the state of values in the document, compute the new value for any field based on it and any other fields, sort and truncate arrays when adding to them and, should you require it automatically, create a new document rather than modify an existing one.

It is this "smart" update capability that makes MongoDB capable of being a principal, "transactional" database in large, multi-user systems versus a simple store of document shaped data.

These three features, at the heart of an end-to-end data platform, are what genuinely make MongoDB my personal first choice when I want to build a system to support many users with a snappy user experience, 24 hours a day, 365 days a year.


Distinguished Engineer and 25 year NoSQL veteran John Page explains in 5 minutes why MongoDB would be his first choice for building a multi-user application.

Paginations 2.0: Why I Would Choose MongoDB

Ken W. Alger

Daniel Coupal



<div class="introduction">

In this edition of the *Building with Patterns* series, we're going to
cover the Bucket Pattern. This pattern is particularly effective when
working with Internet of Things (IoT), Real-Time Analytics, or
Time-Series data in general. By *bucketing* data together we make it
easier to organize specific groups of data, increasing the ability to
discover historical trends or provide future forecasting and optimize
our use of storage.

</div>

<div class="content">

## The Bucket Pattern

With data coming in as a stream over a period of time (time series data)
we may be inclined to store each measurement in its own document.
However, this inclination is a very relational approach to handling the
data. If we have a sensor taking the temperature and saving it to the
database every minute, our data stream might look something like:

``` javascript
{
   sensor_id: 12345,
   timestamp: ISODate("2019-01-31T10:00:00.000Z"),
   temperature: 40
}

{
   sensor_id: 12345,
   timestamp: ISODate("2019-01-31T10:01:00.000Z"),
   temperature: 40
}

{
   sensor_id: 12345,
   timestamp: ISODate("2019-01-31T10:02:00.000Z"),
   temperature: 41
}
```

This can pose some issues as our application scales in terms of data and
index size. For example, we could end up having to index `sensor_id` and
`timestamp` for every single measurement to enable rapid access at the
cost of RAM. By leveraging the document data model though, we can
"bucket" this data, by time, into documents that hold the measurements
from a particular time span. We can also programmatically add additional
information to each of these "buckets".

By applying the Bucket Pattern to our data model, we get some benefits
in terms of index size savings, potential query simplification, and the
ability to use that pre-aggregated data in our documents. Taking the
data stream from above and applying the Bucket Pattern to it, we would
wind up with:

``` javascript
{
    sensor_id: 12345,
    start_date: ISODate("2019-01-31T10:00:00.000Z"),
    end_date: ISODate("2019-01-31T10:59:59.000Z"),
    measurements: [
       {
       timestamp: ISODate("2019-01-31T10:00:00.000Z"),
       temperature: 40
       },
       {
       timestamp: ISODate("2019-01-31T10:01:00.000Z"),
       temperature: 40
       },
       ...
       {
       timestamp: ISODate("2019-01-31T10:42:00.000Z"),
       temperature: 42
       }
    ],
   transaction_count: 42,
   sum_temperature: 2413
}
```

By using the Bucket Pattern, we have "bucketed" our data to, in this
case, a one hour bucket. This particular data stream would still be
growing as it currently only has 42 measurements; there's still more
measurements for that hour to be added to the "bucket". When they are
added to the `measurements` array, the `transaction_count` will be
incremented and `sum_temperature` will also be updated.

With the pre-aggregated `sum_temperature` value, it then becomes
possible to easily pull up a particular bucket and determine the average
temperature (`sum_temperature / transaction_count`) for that bucket.
When working with time-series data it is frequently more interesting and
important to know what the average temperature was from 2:00 to 3:00 pm
in Corning, California on 13 July 2018 than knowing what the temperature
was at 2:03 pm. By bucketing and doing pre-aggregation we're more able
to easily provide that information.

Additionally, as we gather more and more information we may determine
that keeping all of the source data in an archive is more effective. How
frequently do we need to access the temperature for Corning from 1948,
for example? Being able to move those buckets of data to a data archive
can be a large benefit.

## Sample Use Case

One example of making time-series data valuable in the real world comes
from an [IoT implementation by
Bosch](https://www.mongodb.com/customers/bosch). They are using MongoDB
and time-series data in an automotive field data app. The app captures
data from a variety of sensors throughout the vehicle allowing for
improved diagnostics of the vehicle itself and component performance.

Other examples include major banks that have incorporated this pattern
in financial applications to group transactions together.

</div>

<div class="summary">

## Conclusion

When working with time-series data, using the Bucket Pattern in MongoDB
is a great option. It reduces the overall number of documents in a
collection, improves index performance, and by leveraging
pre-aggregation, it can simplify data access.

The Bucket Design pattern works great for many cases. But what if there
are outliers in our data? That's where the next pattern we'll discuss,
the [Outlier Design
Pattern](/how-to/outlier-pattern), comes into play.

</div>


Over the course of this blog post series, we'll take a look at twelve common Schema Design Patterns that work well in MongoDB.

Building with Patterns: The Bucket Pattern



<div class="introduction">

Welcome back to the Building with Patterns series. Last time we looked
at the [Polymorphic Pattern](/how-to/polymorphic-pattern) which covers
situations when all documents in a collection are of similar, but not
identical, structure. In this post, we'll take a look at the Attribute
Pattern.

The Attribute Pattern is particularly well suited when:

-   We have big documents with many similar fields but there is a subset
    of fields that share common characteristics and we want to sort or
    query on that subset of fields, *or*
-   The fields we need to sort on are only found in a small subset of
    documents, *or*
-   Both of the above conditions are met within the documents.

For performance reasons, to optimize our search we'd likely need many
indexes to account for all of the subsets. Creating all of these indexes
could reduce performance. The Attribute Pattern provides a good solution
for these cases.

</div>

<div class="content">

## The Attribute Pattern

Let's think about a collection of movies. The documents will likely have
similar fields involved across all of the documents: title, director,
producer, cast, etc. Let's say we want to search on the release date. A
challenge that we face when doing so, is *which* release date? Movies
are often released on different dates in different countries.

``` javascript
{
    title: "Star Wars",
    director: "George Lucas",
    ...
    release_US: ISODate("1977-05-20T01:00:00+01:00"),
    release_France: ISODate("1977-10-19T01:00:00+01:00"),
    release_Italy: ISODate("1977-10-20T01:00:00+01:00"),
    release_UK: ISODate("1977-12-27T01:00:00+01:00"),
    ...
}
```

A search for a release date will require looking across many fields at
once. In order to quickly do searches for release dates, we'd need
several indexes on our movies collection:

``` javascript
{release_US: 1}
{release_France: 1}
{release_Italy: 1}
...
```

By using the Attribute Pattern, we can move this subset of information
into an array and reduce the indexing needs. We turn this information
into an array of key-value pairs:

``` javascript
{
    title: "Star Wars",
    director: "George Lucas",
    ...
    releases: [
        {
        location: "USA",
        date: ISODate("1977-05-20T01:00:00+01:00")
        },
        {
        location: "France",
        date: ISODate("1977-10-19T01:00:00+01:00")
        },
        {
        location: "Italy",
        date: ISODate("1977-10-20T01:00:00+01:00")
        },
        {
        location: "UK",
        date: ISODate("1977-12-27T01:00:00+01:00")
        },
        ...
    ],
    ...
}
```

Indexing becomes much more manageable by creating one index on the
elements in the array:

``` javascript
{ "releases.location": 1, "releases.date": 1}
```

By using the Attribute Pattern we can add organization to our documents
for common characteristics and account for rare/unpredictable fields.
For example, a movie released in a new or small festival. Further,
moving to a key/value convention allows for the use of non-deterministic
naming and the easy addition of qualifiers. For example, if our data
collection was on bottles of water, our attributes might look something
like:

``` javascript
"specs": [
    { k: "volume", v: "500", u: "ml" },
    { k: "volume", v: "12", u: "ounces" }
]
```

Here we break the information out into keys and values, "k" and "v", and
add in a third field, "u" which allows for the units of measure to be
stored separately.

``` javascript
{"specks.k": 1, "specs.v": 1, "specs.u": 1}
```

## Sample Use Case

The Attribute Pattern is well suited for schemas that have sets of
fields that have the same value type, such as lists of dates. It also
works well when working with the characteristics of products. Some
products, such as clothing, may have sizes that are expressed in small,
medium, or large. Other products in the same collection may be expressed
in volume. Yet others may be expressed in physical dimensions or weight.

A customer in the domain of asset management recently deployed their
solution using the Attribute Pattern. The customer uses the pattern to
store all characteristics of a given asset. These characteristics are
seldom common across the assets or are simply difficult to predict at
design time. Relational models typically use a complicated design
process to express the same idea in the form of [user-defined
fields](https://martinfowler.com/bliki/UserDefinedField.html).

While many of the fields in the product catalog are similar, such as
name, vendor, manufacturer, country of origin, etc., the specifications,
or attributes, of the item may differ. If your application and data
access patterns rely on searching through many of these different fields
at once, the Attribute Pattern provides a good structure for the data.

</div>

<div class="summary">

## Conclusion

The Attribute Pattern provides for easier indexing the documents,
targeting many similar fields per document. By moving this subset of
data into a key-value sub-document, we can use non-deterministic field
names, add additional qualifiers to the information, and more clearly
state the relationship of the original field and value. When we use the
Attribute Pattern, we need fewer indexes, our queries become simpler to
write, and our queries become faster.

</div>


Building with Patterns: The Attribute Pattern



<div class="introduction">

Some years ago, the first PCs had a whopping 256KB of RAM and dual 5.25"
floppy drives. No hard drives as they were incredibly expensive at the
time. These limitations resulted in having to physically swap floppy
disks due to a lack of memory when working with large (for the time)
amounts of data. If only there was a way back then to only bring into
memory the data I frequently used, as in a subset of the overall data.

Modern applications aren't immune from exhausting resources. MongoDB
keeps frequently accessed data, referred to as the [working set](https://docs.mongodb.com/manual/reference/glossary/#term-working-set),
in RAM. When the working set of data and indexes grows beyond the
physical RAM allotted, performance is reduced as disk accesses starts to
occur and data rolls out of RAM.

How can we solve this? First, we could add more RAM to the server. That
only scales so much though. We can look at
[sharding](https://docs.mongodb.com/manual/sharding/)
our collection, but that comes with additional costs and complexities
that our application may not be ready for. Another option is to reduce
the size of our working set. This is where we can leverage the Subset
Pattern.

</div>

<div class="content">

## The Subset Pattern

This pattern addresses the issues associated with a working set that
exceeds RAM, resulting in information being removed from memory. This is
frequently caused by large documents which have a lot of data that isn't
actually used by the application. What do I mean by that exactly?

Imagine an e-commerce site that has a list of reviews for a product.
When accessing that product's data it's quite possible that we'd only
need the most recent ten or so reviews. Pulling in the entirety of the
product data with **all** of the reviews could easily cause the working
set to expand.

<img src="https://www.mongodb.com/developer/images/how-to/design-patterns/subset-full-doc.png" class="align-center" alt="A full document with reviews" />

Instead of storing all the reviews with the product, we can split the
collection into two collections. One collection would have the most
frequently used data, e.g. current reviews and the other collection
would have less frequently used data, e.g. old reviews, product history,
etc. We can duplicate part of a 1-N or N-N relationship that is used by
the most used side of the relationship.

<img src="https://www.mongodb.com/developer/images/how-to/design-patterns/subset-two-collections.png" class="align-center" alt="A document with a subset and a full review collection" />

In the **Product** collection, we'll only keep the ten most recent
reviews. This allows the working set to be reduced by only bringing in a
portion, or subset, of the overall data. The additional information,
reviews in this example, are stored in a separate **Reviews** collection
that can be accessed if the user wants to see additional reviews. When
considering where to split your data, the most used part of the document
should go into the "main" collection and the less frequently used data
into another. For our reviews, that split might be the number of reviews
visible on the product page.

## Sample Use Case

The Subset Pattern is very useful when we have a large portion of data
inside a document that is rarely needed. Product reviews, article
comments, actors in a movie are all examples of use cases for this
pattern. Whenever the document size is putting pressure on the size of
the working set and causing the working set to exceed the computer's RAM
capacities, the Subset Pattern is an option to consider.

</div>

<div class="summary">

## Conclusion

By using smaller documents with more frequently accessed data, we reduce
the overall size of the working set. This allows for shorter disk access
times for the most frequently used information that an application
needs. One tradeoff that we must make when using the Subset Pattern is
that we must manage the subset and also if we need to pull in older
reviews or all of the information, it will require additional trips to
the database to do so.

The next post in this series will look at the features and benefits of
the [Extended Reference Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-extended-reference-pattern).

</div>


Building with Patterns: The Subset Pattern

Lauren Schaefer

Discover 7 things Lauren learned while modeling data in MongoDB.

7 Things I Learned While Modeling Data for YouTube Stats

Joe Karlsson

Have you ever wondered, "How do I model a MongoDB database schema for my application?" This post answers all your questions!

MongoDB Schema Design Best Practices

We've reached the final post in this series on MongoDB schema design anti-patterns. You're an expert now, right? We hope so. But don't worry—even if you fall into the trap of accidentally implementing an anti-pattern, [MongoDB Atlas](https://www.mongodb.com/cloud/atlas) can help you identify it.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/trap_05f118ddc4.gif"
        alt="It's a trap"
    />
</figure>

## The Anti-Patterns

Below is a brief description of each of the schema design anti-patterns we've covered in this series.

-   [Massive arrays](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays): storing massive, unbounded arrays in your documents.
-   [Massive number of collections](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-number-collections): storing a massive number of collections (especially if they are unused or unnecessary) in your database.
-   [Unnecessary indexes](https://developer.mongodb.com/article/schema-design-anti-pattern-unnecessary-indexes): storing an index that is unnecessary because it is (1) rarely used if at all or (2) redundant because another compound index covers it.
-   [Bloated documents](https://developer.mongodb.com/article/schema-design-anti-pattern-bloated-documents): storing large amounts of data together in a document when that data is not frequently accessed together.
-   [Separating data that is accessed together](https://developer.mongodb.com/article/schema-design-anti-pattern-separating-data): separating data between different documents and collections that is frequently accessed together.
-   [Case-insensitive queries without case-insensitive indexes](https://developer.mongodb.com/article/schema-design-anti-pattern-case-insensitive-query-index): frequently executing a case-insensitive query without having a case-insensitive index to cover it.

>
>
>:youtube[]{vid=8CZs-0it9r4 list=PL4RCxklHWZ9uluV0YBxeuwpEa0FWdmCRy}
>
>If you'd like to learn more about each of the anti-patterns, check out this YouTube playlist.
>
>

## Building Your Data Modeling Foundation

Now that you know what **not** to do, let's talk about what you **should** do instead. Begin by learning the MongoDB schema design patterns. [Ken Alger](https://twitter.com/kenwalger) and [Daniel Coupal](https://twitter.com/danielcoupal) wrote a fantastic [blog series](https://www.mongodb.com/blog/post/building-with-patterns-a-summary) that details each of the 12 patterns. Daniel also co-created a [free MongoDB University Course](https://university.mongodb.com/courses/M320/about) that walks you through how to model your data.

Once you have built your data modeling foundation on schema design patterns and anti-patterns, carefully consider your use case:

-   What data will you need to store?
-   What data is likely to be accessed together?
-   What queries will be run most frequently?
-   What data is likely to grow at a rapid, unbounded pace?

The great thing about MongoDB is that it has a flexible schema. You have the power to rapidly make changes to your data model when you use MongoDB. If your initial data model turns out to be not so great or your application's requirements change, you can easily update your data model. And you can make those updates without any downtime! Check out the [Schema Versioning Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-schema-versioning-pattern) for more details.

If and when you're ready to lock down part or all of your schema, you can add [schema validation](https://docs.mongodb.com/manual/core/schema-validation/).  Don't worry—the schema validation is flexible too. You can configure it to throw warnings or errors. You can also choose if the validation should apply to all documents or just documents that already pass the schema validation rules. All of this flexibility gives you the ability to validate documents with different shapes in the same collection, helping you migrate your schema from one version to the next.

## Spotting Anti-Patterns in Your Database

Hopefully, you'll keep all of the schema design patterns and anti-patterns top-of-mind while you're planning and modifying your database schema. But maybe that's wishful thinking. We all make mistakes.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/problem_134ee74603.gif"
        alt="Oh my God. I am part of the problem"
    />
</figure>

If your database is hosted on [MongoDB Atlas](https://www.mongodb.com/cloud/atlas), you can get some help spotting anti-patterns. Navigate to the Performance Advisor (available in M10 clusters and above) or the Data Explorer (available in all clusters) and look for the Schema Anti-Patterns panel. These Schema Anti-Patterns panels will display a list of anti-patterns in your collections and provide pointers on how to fix the issues.

![The Schema Anti-Patterns panel in the Atlas Data Explorer shows one issue found: use case-insensitive indexes.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/anti_patterns_panel_1fddb7c8ef.png "The Schema Anti-Patterns panel in the Atlas Data Explorer shows one issue found: use case-insensitive indexes.")

To learn more, check out Marissa Jasso's [blog post](https://www.mongodb.com/blog/post/schema-suggestions-atlas-years-best-practices-instantly-available-to-you) that details this handy schema suggestion feature or watch her demo below.

:youtube[]{vid=XFJcboyDSRA}


## Summary

Every use case is unique, so every schema will be unique. No formula exists for determining the "right" model for your data in MongoDB.

Give yourself a solid data modeling foundation by learning the MongoDB schema design patterns and anti-patterns. Then begin modeling your data, carefully considering the details of your particular use case and leveraging the principles of the patterns and anti-patterns.

So, get pumped, have fun, and model some data!

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/pumped_7f744a93ab.gif"
        alt="Do it. Fierce. Power"
    />
</figure>

>When you're ready to build a schema in MongoDB, check out [MongoDB Atlas](http://bit.ly/MDB_Atlas), MongoDB's fully managed database-as-a-service. Atlas is the easiest way to get started with MongoDB and has a generous, forever-free tier.

## Related Links

Check out the following resources for more information:

-   [Blog Series: Building with Patterns: A Summary](https://www.mongodb.com/blog/post/building-with-patterns-a-summary)
-   [MongoDB University Course M320: Data Modeling](https://university.mongodb.com/courses/M320/about)
-   [MongoDB Docs: Schema Validation](https://docs.mongodb.com/manual/core/schema-validation/)
-   [Blog Post: JSON Schema Validation - Locking Down Your Model the Smart Way](https://www.mongodb.com/blog/post/json-schema-validation--locking-down-your-model-the-smart-way)
-   [Blog Post: Schema Suggestions in MongoDB Atlas: Years of Best Practices, Instantly Available To You](https://www.mongodb.com/blog/post/schema-suggestions-atlas-years-best-practices-instantly-available-to-you)
-   [MongoDB Docs: Improve Your Schema](https://docs.atlas.mongodb.com/performance-advisor/schema-advisor/)


Get a summary of the six MongoDB Schema Design Anti-Patterns. Plus, learn how MongoDB Atlas can help you spot the anti-patterns in your databases.

A Summary of Schema Design Anti-Patterns and How to Spot Them

Welcome (or welcome back!) to the MongoDB Schema Anti-Patterns series!  We're halfway through the series. So far, we've discussed three anti-patterns: [massive arrays](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays), [massive number of collections](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-number-collections), and [unnecessary indexes](https://developer.mongodb.com/article/schema-design-anti-pattern-unnecessary-indexes).

Today, let's discuss document size. MongoDB has a [16 MB document size limit](https://docs.mongodb.com/manual/reference/limits/#BSON-Document-Size).  But should you use all 16 MBs? Probably not. Let's find out why.

>
>
>:youtube[]{vid=mHeP5IbozDU  start=389}
>If your brain feels bloated from too much reading, sit back, relax, and watch this video.
>
>


## Bloated Documents

Chances are pretty good that you want your queries to be blazing fast.  MongoDB wants your queries to be blazing fast too.

To keep your queries running as quickly as possible, [WiredTiger](https://docs.mongodb.com/manual/core/wiredtiger/) (the default storage engine for MongoDB) keeps all of the indexes plus the documents that are accessed the most frequently in memory. We refer to these frequently accessed documents and index pages as the working set.  When the working set fits in the RAM allotment, MongoDB can query from memory instead of from disk. Queries from memory are faster, so the goal is to keep your most popular documents small enough to fit in the RAM allotment.

The working set's RAM allotment is the larger of:

-   50% of (RAM - 1 GB)
-   256 MB.

For more information on the storage specifics, see [Memory Use](https://docs.mongodb.com/manual/core/wiredtiger/#memory-use). If you're using MongoDB Atlas to host your database, see [Atlas Sizing and Tier Selection: Memory](https://docs.atlas.mongodb.com/sizing-tier-selection/#memory).

One of the rules of thumb you'll hear frequently when discussing MongoDB schema design is *data that is accessed together should be stored together*. Note that it doesn't say *data that is related to each other should be stored together*.

Sometimes data that is related to each other isn't actually accessed together. You might have large, bloated documents that contain information that is related but not actually accessed together frequently. In that case, separate the information into smaller documents in separate collections and use [references](https://docs.mongodb.com/manual/reference/database-references) to connect those documents together.

The opposite of the Bloated Documents Anti-Pattern is the Subset Pattern. The Subset Pattern encourages the use of smaller documents that contain the most frequently accessed data. Check out [this post on the Subset Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-subset-pattern) to learn more about how to successfully leverage this pattern.

## Example

Let's revisit Leslie's website for inspirational women that we discussed in the [previous post](https://developer.mongodb.com/article/schema-design-anti-pattern-unnecessary-indexes).  Leslie updates the home page to display a list of the names of 100 randomly selected inspirational women. When a user clicks on the name of an inspirational woman, they will be taken to a new page with all of the detailed biographical information about the woman they selected. Leslie fills the website with 4,704 inspirational women—including herself.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/inspiration_db8146b624.gif"
        alt="Leslie says she's big enough to admit that she's often inspired by herself"
    />
</figure>

Initially, Leslie decides to create one collection named InspirationalWomen, and creates a document for each inspirational woman.  The document contains all of the information for that woman. Below is a document she creates for Sally Ride.

``` none
// InspirationalWomen collection

{
   "_id": {
      "$oid": "5ec81cc5b3443e0e72314946"
   },
   "first_name": "Sally",
   "last_name": "Ride",
   "birthday": 1951-05-26T00:00:00.000Z,
   "occupation": "Astronaut",
   "quote": "I would like to be remembered as someone who was not afraid to do
             what she wanted to do, and as someone who took risks along the 
             way in order to achieve her goals.",
   "hobbies": [
      "Tennis",
      "Writing children's books"
   ],
   "bio": "Sally Ride is an inspirational figure who... ", 
   ...
}
```

Leslie notices that her home page is lagging. The home page is the most visited page on her site, and, if the page doesn't load quickly enough, visitors will abandon her site completely.

Leslie is hosting her database on [MongoDB Atlas](https://cloud.mongodb.com) and is using an [M10 dedicated cluster](https://docs.atlas.mongodb.com/cluster-tier/). With an M10, she gets 2 GB of RAM. She does some quick calculations and discovers that her working set needs to fit in 0.5 GB. (Remember that her working set can be up to 50% of (2 GB RAM - 1 GB) = 0.5 GB or 256 MB, whichever is larger).

Leslie isn't sure if her working set will currently fit in 0.5 GB of RAM, so she navigates to the [Atlas Data Explorer](https://docs.atlas.mongodb.com/data-explorer/databases-collections/).  She can see that her InspirationalWomen collection is 580.29 MB and her index size is 196 KB. When she adds those two together, she can see that she has exceeded her 0.5 GB allotment.

![The Atlas Data Explorer shows the size of the InspirationalWomen collection is 580.29 MB and the size of its three associated indexes is 196 KB.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/original_docs_6e36727683.png "The Atlas Data Explorer shows the size of the InspirationalWomen collection is 580.29 MB and the size of its three associated indexes is 196 KB.")

Leslie has two choices: she can restructure her data according to [the Subset Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-subset-pattern) to remove the bloated documents, or she can move up to a M20 dedicated cluster, which has 4 GB of RAM. Leslie considers her options and decides that having the home page and the most popular inspirational women's documents load quickly is most important. She decides that having the less frequently viewed women's pages take slightly longer to load is fine.

She begins determining how to restructure her data to optimize for performance. The query on Leslie's homepage only needs to retrieve each woman's first name and last name. Having this information in the working set is crucial. The other information about each woman (including a lengthy bio) doesn't necessarily need to be in the working set.

To ensure her home page loads at a blazing fast pace, she decides to break up the information in her `InspirationalWomen` collection into two collections: `InspirationalWomen_Summary` and `InspirationalWomen_Details`. She creates a [manual reference](https://docs.mongodb.com/manual/reference/database-references/#manual-references) between the matching documents in the collections. Below are her new documents for Sally Ride.

``` none
// InspirationalWomen_Summary collection

{
   "_id": {
      "$oid": "5ee3b2a779448b306938af0f" 
   },
   "inspirationalwomen_id": {
      "$oid": "5ec81cc5b3443e0e72314946"
   },
   "first_name": "Sally",
   "last_name": "Ride"
}
```

``` none
// InspirationalWomen_Details collection

{
   "_id": {
      "$oid": "5ec81cc5b3443e0e72314946"
   },
   "first_name": "Sally",
   "last_name": "Ride",
   "birthday": 1951-05-26T00:00:00.000Z,
   "occupation": "Astronaut",
   "quote": "I would like to be remembered as someone who was not afraid to do
             what she wanted to do, and as someone who took risks along the 
             way in order to achieve her goals.",
   "hobbies": [
      "Tennis",
      "Writing children's books"
   ],
   "bio": "Sally Ride is an inspirational figure who... ", 
   ...
}
```

Leslie updates her query on the home page that retrieves each woman's first name and last name to use the `InspirationalWomen_Summary` collection. When a user selects a woman to learn more about, Leslie's website code will query for a document in the `InspirationalWomen_Details` collection using the id stored in the `inspirationalwomen_id` field.

Leslie returns to Atlas and inspects the size of her databases and collections. She can see that the total index size for both collections is 276 KB (180 KB + 96 KB). She can also see that the size of her `InspirationalWomen_Summary` collection is about 455 KB. The sum of the indexes and this collection is about 731 KB, which is significantly less than her working set's RAM allocation of 0.5 GB. Because of this, many of the most popular documents from the `InspirationalWomen_Details` collection will also fit in the working set.

![The Atlas Data Explorer shows the total index size for the entire database is 276 KB and the size of the InspirationalWomen_Summary collection is 454.78 KB.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/updated_docs_df1e8f0051.png "The Atlas Data Explorer shows the total index size for the entire database is 276 KB and the size of the InspirationalWomen_Summary collection is 454.78 KB.")

In the example above, Leslie is duplicating all of the data from the `InspirationalWomen_Summary` collection in the `InspirationalWomen_Details` collection. You might be cringing at the idea of data duplication. Historically, data duplication has been frowned upon due to space constraints as well as the challenges of keeping the data updated in both collections. Storage is relatively cheap, so we don't necessarily need to worry about that here.  Additionally, the data that is duplicated is unlikely to change very often.

In most cases, you won't need to duplicate all of the information in more than one collection; you'll be able to store some of the information in one collection and the rest of the information in the other. It all depends on your use case and how you are using the data.

## Summary

Be sure that the indexes and the most frequently used documents fit in the RAM allocation for your database in order to get blazing fast queries. If your working set is exceeding the RAM allocation, check if your documents are bloated with extra information that you don't actually need in the working set. Separate frequently used data from infrequently used data in different collections to optimize your performance.

Check back soon for the next post in this schema design anti-patterns series!

## Related Links

Check out the following resources for more information:

-   [MongoDB Docs: Reduce the Size of Large Documents](https://docs.atlas.mongodb.com/schema-advisor/reduce-document-size/)
-   [MongoDB Docs: 16 MB Document Size Limit](https://docs.mongodb.com/manual/reference/limits/#BSON-Document-Size)
-   [MongoDB Docs: Atlas Sizing and Tier Selection](https://docs.atlas.mongodb.com/sizing-tier-selection/)
-   [MongoDB Docs: Model One-to-Many Relationships with Document References](https://docs.mongodb.com/manual/tutorial/model-referenced-one-to-many-relationships-between-documents/)
-   [MongoDB University M320: Data Modeling](https://university.mongodb.com/courses/M320/about)
-   [Blog Series: Building with Patterns](https://www.mongodb.com/blog/post/building-with-patterns-a-summary)
-   [Blog: The Subset Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-subset-pattern)


Don't fall into the trap of this MongoDB Schema Design Anti-Pattern: Bloated Documents

Bloated Documents



<div class="introduction">

So far in this *Building with Patterns* series, we've looked at the
[Polymorphic](/how-to/polymorphic-pattern),
[Attribute](/how-to/attribute-pattern), and
[Bucket](/how-to/bucket-pattern) patterns. While the document schema in
these patterns has slight variations, from an application and query
standpoint, the document structures are fairly consistent. What happens,
however, when this isn't the case? What happens when there is data that
falls outside the "normal" pattern? What if there's an outlier?

Imagine you are starting an e-commerce site that sells books. One of the
queries you might be interested in running is "who has purchased a
particular book". This could be useful for a recommendation system to
show your customers similar books of interest. You decide to store the
`user_id` of a customer in an array for each book. Simple enough, right?

Well, this may indeed work for 99.99% of the cases, but what happens
when J.K. Rowling releases a new Harry Potter book and sales spike in
the millions? The 16MB [BSON document](https://www.mongodb.com/json-and-bson) size limit could easily
be reached. Redesigning our entire application for this *outlier*
situation could result in reduced performance for the typical book, but
we do need to take it into consideration.

</div>

<div class="content">

## The Outlier Pattern

With the Outlier Pattern, we are working to prevent a few queries or
documents driving our solution towards one that would not be optimal for
the majority of our use cases. Not every book sold will sell millions of
copies.

A typical `book` document storing `user_id` information might look
something like:

``` javascript
{
    "_id": ObjectID("507f1f77bcf86cd799439011")
    "title": "A Genealogical Record of a Line of Alger",
    "author": "Ken W. Alger",
    ...,
    "customers_purchased": ["user00", "user01", "user02"]

}
```

This would work well for a large majority of books that aren't likely to
reach the "best seller" lists. Accounting for outliers though results in
the `customers_purchased` array expanding beyond a 1000 item limit we
have set, we'll add a new field to "flag" the book as an outlier.

``` javascript
{
    "_id": ObjectID("507f191e810c19729de860ea"),
    "title": "Harry Potter, the Next Chapter",
    "author": "J.K. Rowling",
    ...,
   "customers_purchased": ["user00", "user01", "user02", ..., "user999"],
   "has_extras": "true"
}
```

We'd then move the overflow information into a separate document linked
with the book's `id`. Inside the application, we would be able to
determine if a document has a `has_extras` field with a value of `true`.
If that is the case, the application would retrieve the extra
information. This could be handled so that it is rather transparent for
most of the application code.

Many design decisions will be based on the application workload, so this
solution is intended to show an example of the Outlier Pattern. The
important concept to grasp here is that the outliers have a substantial
enough difference in their data that, if they were considered "normal",
changing the application design for them would degrade performance for
the more typical queries and documents.

## Sample Use Case

The Outlier Pattern is an advanced pattern, but one that can result in
large performance improvements. It is frequently used in situations when
popularity is a factor, such as in social network relationships, book
sales, movie reviews, etc. The Internet has transformed our world into a
much smaller place and when something becomes popular, it transforms the
way we need to model the data around the item.

One example is a customer that has a video conferencing product. The
list of authorized attendees in most video conferences can be kept in
the same document as the conference. However, there are a few events,
like a company's all hands, that have thousands of expected attendees.
For those outlier conferences, the customer implemented "overflow"
documents to record those long lists of attendees.

</div>

<div class="summary">

## Conclusion

The problem that the Outlier Pattern addresses is preventing a few
documents or queries to determine an application's solution. Especially
when that solution would not be optimal for the majority of use cases.
We can leverage MongoDB's flexible data model to add a field to the
document "flagging" it as an outlier. Then, inside the application, we
handle the outliers slightly differently. By tailoring your schema for
the typical document or query, application performance will be optimized
for those normal use cases and the outliers will still be addressed.

One thing to consider with this pattern is that it often is tailored for
specific queries and situations. Therefore, ad hoc queries may result in
less than optimal performance. Additionally, as much of the work is done
within the application code itself, additional code maintenance may be
required over time.

In our next *Building with Patterns* post, we'll take a look at the
[Computed Pattern](/how-to/computed-pattern) and how to optimize schema for
applications that can result in unnecessary waste of resources.
 
</div>


Building with Patterns: The Outlier Pattern



<div class="introduction">

## Introduction

One frequently asked question when it comes to MongoDB is "How do I
structure my schema in MongoDB for my application?" The honest answer
is, it depends. Does your application do more reads than writes? What
data needs to be together when read from the database? What performance
considerations are there? How large are the documents? How large will
they get? How do you anticipate your data will grow and scale?

All of these questions, and more, factor into how one designs a database
schema in MongoDB. It has been said that MongoDB is schemaless. In fact,
schema design is very important in MongoDB. The hard fact is that most
performance issues we've found trace back to poor schema design.

Over the course of this series, Building with Patterns, we'll take a
look at twelve common Schema Design Patterns that work well in MongoDB.
We hope this series will establish a common methodology and vocabulary
you can use when designing schemas. Leveraging these patterns allows for
the use of "building blocks" in schema planning, resulting in more
methodology being used than art.

MongoDB uses a [document data
model](https://docs.mongodb.com/manual/core/data-model-design/). This
model is inherently flexible, allowing for data models to support your
application needs. The flexibility also can lead to schemas being more
complex than they should. When thinking of schema design, we should be
thinking of performance, scalability, and simplicity.

Let's start our exploration into schema design with a look at what can
be thought as the base for all patterns, the *Polymorphic Pattern*. This
pattern is utilized when we have documents that have more similarities
than differences. It's also a good fit for when we want to keep
documents in a single collection.

</div>

<div class="content">

## The Polymorphic Pattern

When all documents in a collection are of similar, but not identical,
structure, we call this the Polymorphic Pattern. As mentioned, the
Polymorphic Pattern is useful when we want to access (query) information
from a single collection. Grouping documents together based on the
queries we want to run (instead of separating the object across tables
or collections) helps improve performance.

Imagine that our application tracks professional sports athletes across
all different sports.

We still want to be able to access all of the athletes in our
application, but the attributes of each athlete are very different. This
is where the Polymorphic Pattern shines. In the example below, we store
data for athletes from two different sports in the same collection. The
data stored about each athlete does not need to be the same even though
the documents are in the same collection.

<img src="https://www.mongodb.com/developer/images/Polymorphic1-nanehssyv3.png" width="760" alt="Polymorphic Design Pattern with Common Fields" />

Professional athlete records have some similarities, but also some
differences. With the Polymorphic Pattern, we are easily able to
accommodate these differences. If we were not using the Polymorphic
Pattern, we might have a collection for Bowling Athletes and a
collection for Tennis Athletes. When we wanted to query on all athletes,
we would need to do a time-consuming and potentially complex join.
Instead, since we are using the Polymorphic Pattern, all of our data is
stored in one Athletes collection and querying for all athletes can be
accomplished with a simple query.

This design pattern can flow into embedded sub-documents as well. In the
above example, Martina Navratilova didn't just compete as a single
player, so we might want to structure her record as follows:

<img src="https://www.mongodb.com/developer/images/Polymorphic2-bpk0w3ig5y.gif" width="760" alt="Polymorphic Design Pattern with sub-documents" />

From an application development standpoint, when using the Polymorphic
Pattern we're going to look at specific fields in the document or
sub-document to be able to track differences. We'd know, for example,
that a tennis player athlete might be involved with different events,
while a different sports player may not be. This will, typically,
require different code paths in the application code based on the
information in a given document. Or, perhaps, different classes or
subclasses are written to handle the differences between tennis,
bowling, soccer, and rugby players.

## Sample Use Case

One example use case of the Polymorphic Pattern is [Single View
applications](https://www.mongodb.com/use-cases/single-view). Imagine
working for a company that, over the course of time, acquires other
companies with their technology and data patterns. For example, each
company has many databases, each modeling "insurances with their
customers" in a different way. Then you buy those companies and want to
integrate all of those systems into one. Merging these different systems
into a unified SQL schema is costly and time-consuming.

[MetLife](https://www.metlife.com/) was able to leverage MongoDB and the
Polymorphic Pattern to build their single view application in a few
months. Their Single View application aggregates data from multiple
sources into a central repository allowing customer service, insurance
agents, billing, and other departments to get a 360° picture of a
customer. This has allowed them to provide better customer service at a
reduced cost to the company. Further, using MongoDB's flexible data
model and the Polymorphic Pattern, the development team was able to
innovate quickly to bring their product online.

A Single View application is one use case of the Polymorphic Pattern. It
also works well for things like product catalogs where a bicycle has
different attributes than a fishing rod. Our athlete example could
easily be expanded into a more full-fledged content management system
and utilize the Polymorphic Pattern there.

</div>

<div class="summary">

## Conclusion

The Polymorphic Pattern is used when documents have more similarities
than they have differences. Typical use cases for this type of schema
design would be:

-   Single View applications
-   Content management
-   Mobile applications
-   A product catalog

The Polymorphic Pattern provides an easy-to-implement design that allows
for querying across a single collection and is a starting point for many
of the design patterns we'll be exploring in upcoming posts. The next
pattern we'll discuss is the
[Attribute Pattern](/developer/products/mongodb/attribute-pattern/).

</div>


Building with Patterns: The Polymorphic Pattern

Katya Kamenieva

Learn about improved error messages for schema validation in MongoDB 5.0.

Improved Error Messages for Schema Validation in MongoDB 5.0

Design patterns are a fundamental part of software engineering. They provide developers with best practices and a common language as they architect applications.

At MongoDB, we have schema design patterns to help developers be successful as they plan and iterate on their schema designs. [Daniel Coupal](https://twitter.com/danielcoupal) and [Ken Alger](https://twitter.com/kenwalger) co-wrote a fantastic [blog series](https://www.mongodb.com/blog/post/building-with-patterns-a-summary) that highlights each of the schema design patterns. If you really want to dive into the details (and I recommend you do!), check out [MongoDB University's free course on Data Modeling](https://university.mongodb.com/courses/M320/about).

Sometimes, developers jump right into designing their schemas and building their apps without thinking about best practices. As their apps begin to scale, they realize that things are bad.

![Leslie says 'This is bad'](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/thisisbad_8471d61cc3.gif)

We've identified several common mistakes developers make with MongoDB.  We call these mistakes "schema design anti-patterns."

Throughout this blog series, I'll introduce you to six common anti-patterns. Let's start today with the Massive Arrays anti-pattern.

>
>
>:youtube[]{vid=8CZs-0it9r4 start=236}
>
>Prefer to learn by video? I've got you covered.
>
>

## Massive Arrays

One of the rules of thumb when modeling data in MongoDB is *data that is accessed together should be stored together*. If you'll be retrieving or updating data together frequently, you should probably store it together. Data is commonly stored together by embedding related information in subdocuments or arrays.

The problem is that sometimes developers take this too far and embed massive amounts of information in a single document.

Consider an example where we store information about employees who work in various government buildings. If we were to embed the employees in the building document, we might store our data in a buildings collection like the following:

``` javascript
// buildings collection
{
   "_id": "city_hall",
   "name": "City Hall",
   "city": "Pawnee",
   "state": "IN",
   "employees": [
      {
         "_id": 123456789,
         "first": "Leslie",
         "last": "Yepp",
         "cell": "8125552344",
         "start-year": "2004"
      },
      {
         "_id": 234567890,
         "first": "Ron",
         "last": "Swandaughter",
         "cell": "8125559347",
         "start-year": "2002"
      }
   ]
}
```

In this example, the employees array is unbounded. As we begin storing information about all of the employees who work in City Hall, the employees array will become massive—potentially sending us over the [16 mb document maximum](https://docs.mongodb.com/manual/reference/limits/#BSON%20Document%20Size).  Additionally, reading and building indexes on arrays gradually becomes less performant as array size increases.

The example above is an example of the massive arrays anti-pattern.

So how can we fix this?

Instead of embedding the employees in the buildings documents, we could flip the model and instead embed the buildings in the employees documents:

``` javascript
// employees collection
{
   "_id": 123456789,
   "first": "Leslie",
   "last": "Yepp",
   "cell": "8125552344",
   "start-year": "2004",
   "building": {
      "_id": "city_hall",
      "name": "City Hall",
      "city": "Pawnee",
      "state": "IN"
   }
},
{
   "_id": 234567890,
   "first": "Ron",
   "last": "Swandaughter",
   "cell": "8125559347",
   "start-year": "2002",
   "building": {
      "_id": "city_hall",
      "name": "City Hall",
      "city": "Pawnee",
      "state": "IN"
   }
}
```

In the example above, we are repeating the information about City Hall in the document for each City Hall employee. If we are frequently displaying information about an employee and their building in our application together, this model probably makes sense.

The disadvantage with this approach is we have a lot of data duplication. Storage is cheap, so data duplication isn't necessarily a problem from a storage cost perspective. However, every time we need to update information about City Hall, we'll need to update the document for every employee who works there. If we take a look at the information we're currently storing about the buildings, updates will likely be very infrequent, so this approach may be a good one.

If our use case does not call for information about employees and their building to be displayed or updated together, we may want to instead separate the information into two collections and [use references](https://docs.mongodb.com/manual/core/data-model-design/#data-modeling-referencing) to link them:

``` javascript
// buildings collection
{
   "_id": "city_hall",
   "name": "City Hall",
   "city": "Pawnee",
   "state": "IN"
}

// employees collection
{
   "_id": 123456789,
   "first": "Leslie",
   "last": "Yepp",
   "cell": "8125552344",
   "start-year": "2004",
   "building_id": "city_hall"
},
{
   "_id": 234567890,
   "first": "Ron",
   "last": "Swandaughter",
   "cell": "8125559347",
   "start-year": "2002",
   "building_id": "city_hall"
}
```

Here we have completely separated our data. We have eliminated massive arrays, and we have no data duplication.

The drawback is that if we need to retrieve information about an employee and their building together, we'll need to use $lookup to join the data together. $lookup operations can be expensive, so it's important to consider how often you'll need to perform $lookup if you choose this option.

If we find ourselves frequently using [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html), another option is to use the [extended reference pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-extended-reference-pattern).  The extended reference pattern is a mixture of the previous two approaches where we duplicate some—but not all—of the data in the two collections. We only duplicate the data that is frequently accessed together.

For example, if our application has a user profile page that displays information about the user as well as the name of the building and the state where they work, we may want to embed the building name and state fields in the employee document:

``` javascript
// buildings collection
{
   "_id": "city_hall",
   "name": "City Hall",
   "city": "Pawnee",
   "state": "IN"
}

// employees collection
{
   "_id": 123456789,
   "first": "Leslie",
   "last": "Yepp",
   "cell": "8125552344",
   "start-year": "2004",
   "building": {
      "name": "City Hall",
      "state": "IN"
   }
},
{
   "_id": 234567890,
   "first": "Ron",
   "last": "Swandaughter",
   "cell": "8125559347",
   "start-year": "2002",
   "building": {
      "name": "City Hall",
      "state": "IN"
   }
}
```

As we saw when we duplicated data previously, we should be mindful of duplicating data that will frequently be updated. In this particular case, the name of the building and the state the building is in are very unlikely to change, so this solution works.

## Summary

Storing related information that you'll be frequently querying together is generally good. However, storing information in massive arrays that will continue to grow over time is generally bad.

As is true with all MongoDB schema design patterns and anti-patterns, carefully consider your use case—the data you will store and how you will query it—in order to determine what schema design is best for you.

Be on the lookout for more posts in this anti-patterns series in the coming weeks.

>
>
>When you're ready to build a schema in MongoDB, check out [MongoDB Atlas](http://bit.ly/MDB_Atlas), MongoDB's fully managed database-as-a-service. Atlas is the easiest way to get started with MongoDB. With a forever-free tier, you're on your way to realizing the full value of MongoDB.
>
>

## Related Links

Check out the following resources for more information:

-   [MongoDB Docs: Unbounded Arrays Anti-Pattern](https://dochub.mongodb.org/core/anti-pattern-unbounded-arrays)
-   [MongoDB Docs: Data Modeling Introduction](https://docs.mongodb.com/manual/core/data-modeling-introduction/)
-   [MongoDB Docs: Model One-to-One Relationships with Embedded Documents](https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-one-relationships-between-documents/)
-   [MongoDB Docs: Model One-to-Many Relationships with Embedded Documents](https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/)
-   [MongoDB Docs: Model One-to-Many Relationships with Document References](https://docs.mongodb.com/manual/tutorial/model-referenced-one-to-many-relationships-between-documents/)
-   [MongoDB University M320: Data Modeling](https://university.mongodb.com/courses/M320/about)
-   [Blog Series: Building with Patterns](https://www.mongodb.com/blog/post/building-with-patterns-a-summary)


Don't fall into the trap of this MongoDB Schema Design Anti-Pattern: Massive Arrays

Massive Arrays

In the [first post in this MongoDB Schema Design Anti-Patterns series](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays), we discussed how we should avoid massive arrays when designing our schemas. But what about having a massive number of [collections](https://docs.mongodb.com/manual/reference/glossary/#term-collection)?  Turns out, they're not great either. In this post, we'll examine why.

>
>
>:youtube[]{vid=8CZs-0it9r4 t=719}
>
>Are you more of a video person? This is for you.
>
>

## Massive Number of Collections

Let's begin by discussing why having a massive number of collections is an anti-pattern. If storage is relatively cheap, who cares how many collections you have?

Every collection in MongoDB [automatically has an index on the \_id field](https://docs.mongodb.com/manual/indexes/#default-id-index). While the size of this index is pretty small for empty or small collections, thousands of empty or unused indexes can begin to drain resources.  Collections will typically have a few more indexes to support efficient queries. All of these indexes add up.

Additionally, [the WiredTiger storage engine](https://docs.mongodb.com/manual/core/wiredtiger/) (MongoDB's default storage engine) stores a file for each collection and a file for each index. WiredTiger will open all files upon startup, so performance will decrease when an excessive number of collections and indexes exist.

In general, we recommend limiting collections to 10,000 per [replica set](https://docs.mongodb.com/manual/reference/glossary/#term-replica-set).  When users begin exceeding 10,000 collections, they typically see decreases in performance.

To avoid this anti-pattern, examine your database and remove unnecessary collections. If you find that you have an increasing number of collections, consider remodeling your data so you have a consistent set of collections.

## Example

Let's take an example from the greatest tv show ever created: Parks and Recreation. Leslie is passionate about maintaining the parks she oversees, and, at one point, she takes it upon herself to remove the trash in the Pawnee River.

Let's say she wants to keep a minute-by-minute record of the water level and temperature of the Pawnee River, the Eagleton River, and the Wamapoke River, so she can look for trends. She could send her coworker Jerry to put 30 sensors in each river and then begin storing the sensor data in a MongoDB database.

One way to store the data would be to create a new collection every day to store sensor data. Each collection would contain documents that store information about one reading for one sensor.

``` javascript
// 2020-05-01 collection
{
   "_id": ObjectId("5eac643e64faf3ff31d70d35"),
   "river": "PawneeRiver",
   "sensor": 1
   "timestamp": "2020-05-01T00:00:00Z",
   "water-level": 61.56,
   "water-temperature": 72.1
},
{
   "_id": ObjectId("5eac643e64faf3ff31d70d36"),
   "river": "PawneeRiver",
   "sensor": 2
   "timestamp": "2020-05-01T00:00:00Z",
   "water-level": 61.55,
   "water-temperature": 72.1
},
...
{
   "_id": ObjectId("5eac643e64faf3ff31d70dfc"),
   "river": "WamapokeRiver",
   "sensor": 90
   "timestamp": "2020-05-01T23:59:00Z",
   "water-level": 72.03,
   "water-temperature": 64.1
}

// 2020-05-02 collection
{
   "_id": ObjectId("5eac644c64faf3ff31d90775"),
   "river": "PawneeRiver",
   "sensor": 1
   "timestamp": "2020-05-02T00:00:00Z",
   "water-level": 63.12,
   "water-temperature": 72.8
},
   {
   "_id": ObjectId("5eac644c64faf3ff31d90776"),
   "river": "PawneeRiver",
   "sensor": 2
   "timestamp": "2020-05-02T00:00:00Z",
   "water-level": 63.11,
   "water-temperature": 72.7
},
...
{
   "_id": ObjectId("5eac644c64faf3ff31d9079c"),
   "river": "WamapokeRiver",
   "sensor": 90
   "timestamp": "2020-05-02T23:59:00Z",
   "water-level": 71.58,
   "water-temperature": 66.2
}
```

Let's say that Leslie wants to be able to easily query on the `river` and `sensor` fields, so she creates an index on each field.

If Leslie were to store hourly data throughout all of 2019 and create two indexes in each collection (in addition to the default index on `_id`), her database would have the following stats:

-   Database size: 5.2 GB
-   Index size: 1.07 GB
-   Total Collections: 365

Each day she creates a new collection and two indexes. As Leslie continues to collect data and her number of collections exceeds 10,000, the performance of her database will decline.

Also, when Leslie wants to look for trends across weeks and months, she'll have a difficult time doing so since her data is spread across multiple collections.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/leslie_grimace_09fbf06a94.gif"
        alt="Leslie makes a grimace"
    />
</figure>

Let's say Leslie realizes this isn't a great schema, so she decides to restructure her data. This time, she decides to keep all of her data in a single collection. She'll [bucket her information](https://docs.mongodb.com/manual/tutorial/model-time-data/#use-buckets-for-time-series-data), so she stores one hour's worth of information from one sensor in each document.

``` javascript
// data collection
{
   "_id": "PawneeRiver-1-2019-05-01T00:00:00.000Z",
   "river": "PawneeRiver",
   "sensor": 1,
   "readings": [
      {
         "timestamp": "2019-05-01T00:00:00.000+00:00",
         "water-level": 61.56,
         "water-temperature": 72.1
      },
      {
         "timestamp": "2019-05-01T00:01:00.000+00:00",
         "water-level": 61.56,
         "water-temperature": 72.1
      },
      ...
      {
         "timestamp": "2019-05-01T00:59:00.000+00:00",
         "water-level": 61.55,
         "water-temperature": 72.0
      }
   ]
},
...
{
   "_id": "PawneeRiver-1-2019-05-02T00:00:00.000Z",
   "river": "PawneeRiver",
   "sensor": 1,
   "readings": [
      {
         "timestamp": "2019-05-02T00:00:00.000+00:00",
         "water-level": 63.12,
         "water-temperature": 72.8
      },
      {
         "timestamp": "2019-05-02T00:01:00.000+00:00",
         "water-level": 63.11,
         "water-temperature": 72.8
      },
      ...
      {
         "timestamp": "2019-05-02T00:59:00.000+00:00",
         "water-level": 63.10,
         "water-temperature": 72.7
      }
   ]
}
...
```

Leslie wants to query on the `river` and `sensor` fields, so she creates two new indexes for this collection.

If Leslie were to store hourly data for all of 2019 using this updated schema, her database would have the following stats:

-   Database size: 3.07 GB
-   Index size: 27.45 MB
-   Total Collections: 1

By restructuring her data, she sees a massive reduction in her index size (1.07 GB initially to 27.45 MB!). She now has a single collection with three indexes.

With this new schema, she can more easily look for trends in her data because it's stored in a single collection. Also, she's using the default index on `_id` to her advantage by storing the hour the water level data was gathered in this field. If she wants to query by hour, she already has an index to allow her to efficiently do so.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/ron_canoe_507863438e.gif"
        alt="Roe canoeing"
    />
</figure>

For more information on modeling time-series data in MongoDB, see [Building with Patterns: The Bucket Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-bucket-pattern).

## Removing Unnecessary Collections

In the example above, Leslie was able to remove unnecessary collections by changing how she stored her data.

Sometimes, you won't immediately know what collections are unnecessary, so you'll have to do some investigating yourself. If you find an empty collection, you can drop it. If you find a collection whose size is made up mostly of indexes, you can probably move that data into another collection and drop the original. You might be able to use [$merge](https://docs.mongodb.com/manual/reference/operator/aggregation/merge/#pipe._S_merge) to move data from one collection to another.

Below are a few ways you can begin your investigation.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/sherlock_traeger_5b99751493.gif"
        alt="Sherlock Traeger"
    />
</figure>

### Using MongoDB Atlas

If your database is hosted in [Atlas](https://cloud.mongodb.com/), navigate to the [Atlas Data Explorer](https://docs.atlas.mongodb.com/data-explorer/#data-explorer).  The Data Explorer allows you to browse a list of your databases and collections. Additionally, you can get stats on your database including the database size, index size, and number of collections.

![The Atlas Data Explorer displays the database size, index size, and total number of collections for the riverstats database.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/atlas_data_explorer_7c2d42e856.png "The Atlas Data Explorer displays the database size, index size, and total number of collections for the riverstats database.")

If you are using an M10 cluster or larger on Atlas, you can also use the [Real-Time Performance Panel](https://docs.atlas.mongodb.com/real-time-performance-panel/#real-time-metrics-status-tab) to check if your application is actively using a collection you're considering dropping.

### Using MongoDB Compass

Regardless of where your MongoDB database is hosted, you can use [MongoDB Compass](https://www.mongodb.com/products/compass), MongoDB's desktop GUI. Similar to the Data Explorer, you can browse your databases and collections so you can check for unused collections. You can also get stats at the database and collection levels.

![Caption: Compass shows the storage size, number of collections, and number of indexes for databases.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/compass_databases_7ff19ce116.png "Caption: Compass shows the storage size, number of collections, and number of indexes for databases.")

![Compass shows the number of documents, average document size, total document size, number of indexes, and total index size for collections in the riverstats database.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/compass_collections_b4b706bed9.png "Compass shows the number of documents, average document size, total document size, number of indexes, and total index size for collections in the riverstats database.")

### Using the Mongo Shell

If you prefer working in a terminal instead of a GUI, connect to your database using the [mongo shell](https://docs.mongodb.com/manual/reference/program/mongo/#bin.mongo).

To see a list of collections, run `db.getCollectionNames()`. Output like the following will be displayed:

``` javascript
[
   "2019-01-01",
   "2019-01-02",
   "2019-01-03",
   "2019-01-04",
   "2019-01-05",
   ...
]
```

To retrieve stats about your database, run `db.stats()`. Output like the following will be displayed:

``` javascript
{
   "db" : "riverstats",
   "collections" : 365,
   "views" : 0,
   "objects" : 47304000,
   "avgObjSize" : 118,
   "dataSize" : 5581872000,
   "storageSize" : 1249677312,
   "numExtents" : 0,
   "indexes" : 1095,
   "indexSize" : 1145790464,
   "scaleFactor" : 1,
   "fsUsedSize" : 5312217088,
   "fsTotalSize" : 10726932480,
   "ok" : 1,
   "$clusterTime" : {
      "clusterTime" : Timestamp(1588795184, 3),
      "signature" : {
         "hash" : BinData(0,"orka3bVeAiwlIGdbVoP+Fj6N01s="),
         "keyId" : NumberLong("6821929184550453250")
      }
   },
   "operationTime" : Timestamp(1588795184, 3)
}
```

You can also run `db.collection.stats()` to see information about a particular collection.

## Summary

Be mindful of creating a massive number of collections as each collection likely has a few indexes associated with it. An excessive number of collections and their associated indexes can drain resources and impact your database's performance. In general, try to limit your replica set to 10,000 collections.

Come back soon for the next post in this anti-patterns series!

>
>
>When you're ready to build a schema in MongoDB, check out [MongoDB Atlas](http://bit.ly/MDB_Atlas), MongoDB's fully managed database-as-a-service. Atlas is the easiest way to get started with MongoDB. With a forever-free tier, you're on your way to realizing the full value of MongoDB.
>
>

## Related Links

Check out the following resources for more information:

-   [MongoDB Docs: Reduce Number of Collections](https://docs.atlas.mongodb.com/schema-advisor/too-many-collections/)
-   [MongoDB Docs: Data Modeling Introduction](https://docs.mongodb.com/manual/core/data-modeling-introduction/)
-   [MongoDB Docs: Use Buckets for Time-Series Data](https://docs.mongodb.com/manual/tutorial/model-time-data/#use-buckets-for-time-series-data)
-   [MongoDB University M320: Data Modeling](https://university.mongodb.com/courses/M320/about)
-   [Blog Series: Building with Patterns](https://www.mongodb.com/blog/post/building-with-patterns-a-summary)


Don't fall into the trap of this MongoDB Schema Design Anti-Pattern: Massive Number of Collections

Massive Number of Collections

So far in this MongoDB Schema Design Anti-Patterns series, we've discussed avoiding [massive arrays](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays) as well as a massive number of [collections](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-number-collections).

Today, let's talk about indexes. Indexes are great (seriously!), but it's easy to get carried away and make indexes that you'll never actually use. Let's examine why an index may be unnecessary and what the consequences of keeping it around are.

>
>
>:youtube[]{vid=mHeP5IbozDU start=32}
>
>Would you rather watch than read? The video above is just for you.
>
>

## Unnecessary Indexes

Before we go any further, we want to emphasize that [indexes](https://docs.mongodb.com/manual/indexes/) are good. Indexes allow MongoDB to efficiently query data. If a query does not have an index to support it, MongoDB performs a collection scan, meaning that it scans *every* document in a collection. Collection scans can be very slow. If you frequently execute a query, make sure you have an index to support it.

Now that we have an understanding that indexes are good, you might be wondering, "Why are unnecessary indexes an anti-pattern? Why not create an index on every field just in case I'll need it in the future?"

We've discovered three big reasons why you should remove unnecessary indexes:

1.  **Indexes take up space**. Each index is at least 8 kB and grows with the number of documents associated with it. Thousands of indexes can begin to drain resources.
2.  **Indexes can impact the storage engine's performance**. As we discussed in the [previous post in this series about the Massive Number of Collections Anti-Pattern](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-number-collections), [the WiredTiger storage engine](https://docs.mongodb.com/manual/core/wiredtiger/) (MongoDB's default storage engine) stores a file for each collection and for each index. WiredTiger will open all files upon startup, so performance will decrease when an excessive number of collections and indexes exist.
3.  **Indexes can impact write performance**. Whenever a document is created, updated, or deleted, any index associated with that document must also be updated. These index updates negatively impact write performance.

In general, we recommend limiting your collection to a maximum of 50 indexes.

To avoid the anti-pattern of unnecessary indexes, examine your database and identify which indexes are truly necessary. Unnecessary indexes typically fall into one of two categories:

1.  The index is rarely used or not at all.
2.  The index is redundant because another [compound index](https://docs.mongodb.com/manual/core/index-compound/) covers it.

## Example

Consider Leslie from the incredible TV show Parks and Recreation. Leslie often looks to other powerful women for inspiration.

Let's say Leslie wants to inspire others, so she creates a website about her favorite inspirational women. The website allows users to search by full name, last name, or hobby.

Leslie chooses to use [MongoDB Atlas](https://cloud.mongodb.com/) to create her database. She creates a collection named `InspirationalWomen`. Inside of that collection, she creates a document for each inspirational woman. Below is a document she created for Sally Ride.

``` javascript
// InspirationalWomen collection

{
   "_id": {
      "$oid": "5ec81cc5b3443e0e72314946"
   },
   "first_name": "Sally",
   "last_name": "Ride",
   "birthday": 1951-05-26T00:00:00.000Z,
   "occupation": "Astronaut",
   "quote": "I would like to be remembered as someone who was not afraid to do what 
             she wanted to do, and as someone who took risks along the way in order 
             to achieve her goals.",
   "hobbies": [
      "Tennis",
      "Writing children's books"
   ]
}
```

Leslie eats several sugar-filled Nutriyum bars, and, riding her sugar high, creates an index for every field in her collection.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/nutriyum_6d9e11364f.gif"
        alt="Leslie says: 'There is a secret ingredient in these Nutriyum bars that make me feel so good.'"
    />
</figure>

She also creates a compound index on the last_name and first_name fields, so that users can search by full name. Leslie now has one collection with eight indexes:

1.  `_id` is indexed by default (see the [MongoDB Docs](https://docs.mongodb.com/manual/indexes/#default-id-index) for more details)
2.  `{ first_name: 1 }`
3.  `{ last_name: 1 }`
4.  `{ birthday: 1 }`
5.  `{ occupation: 1 }`
6.  `{ quote: 1 }`
7.  `{ hobbies: 1 }`
8.  `{ last_name: 1, first_name: 1}`

Leslie launches her website and is excited to be helping others find inspiration. Users are discovering new role models as they search by full name, last name, and hobby.

### Removing Unnecessary Indexes

Leslie decides to fine-tune her database and wonders if all of those indexes she created are really necessary.

She opens the Atlas Data Explorer and navigates to the Indexes pane. She can see that the only two indexes that are being used are the compound index named `last_name_1_first_name_1` and the `hobbies_1` index. She realizes that this makes sense.

Her queries for inspirational women by full name are covered by the `last_name_1_first_name_1` index. Additionally, her query for inspirational women by last name is covered by the same `last_name_1_first_name_1` compound index since the index has a `last_name` [prefix](https://docs.mongodb.com/manual/core/index-compound/#prefixes).  Her queries for inspirational women by hobby are covered by the `hobbies_1` index. Since those are the only ways that users can query her data, the other indexes are unnecessary.

![The Atlas Data Explorer displays index usage and allows users to delete indexes.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/indexes_data_explorer_cropped_27e0296ac8.png "The Atlas Data Explorer displays index usage and allows users to delete indexes.")

In the Data Explorer, Leslie has the option of dropping all of the other unnecessary indexes. Since MongoDB requires an index on the `_id` field, she cannot drop this index.

In addition to using the Data Explorer, Leslie also has the option of using [MongoDB Compass](https://www.mongodb.com/products/compass) to check for unnecessary indexes. When she navigates to the Indexes pane for her collection, she can once again see that the `last_name_1_first_name_1` and the `hobbies_1` indexes are the only indexes being used regularly. Just as she could in the Atlas Data Explorer, Leslie has the option of dropping each of the indexes except for `_id`.

![Compass displays index usage and allows users to delete indexes.](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/indexes_compass_d2007f1072.png "Compass displays index usage and allows users to delete indexes.")

Leslie decides to drop all of the unnecessary indexes. After doing so, her collection now has the following indexes:

1.  `_id` is indexed by default
2.  `{ hobbies: 1 }`
3.  `{ last_name: 1, first_name: 1}`

## Summary

Creating indexes that support your queries is good. Creating unnecessary indexes is generally bad.

Unnecessary indexes reduce performance and take up space. An index is considered to be unnecessary if (1) it is not frequently used by a query or (2) it is [redundant because another compound index covers it](https://docs.mongodb.com/manual/core/index-compound/#prefixes).

You can use the Atlas Data Explorer or MongoDB Compass to help you discover how frequently your indexes are being used. When you discover an index is unnecessary, remove it.

Be on the lookout for the next post in this anti-patterns series!

## Related Links

Check out the following resources for more information:

-   [MongoDB Docs: Remove Unnecessary Indexes](https://docs.atlas.mongodb.com/schema-advisor/too-many-indexes/#indexes-anti-pattern)
-   [MongoDB Docs: Indexes](https://docs.mongodb.com/manual/indexes/)
-   [MongoDB Docs: Compound Indexes — Prefixes](https://docs.mongodb.com/manual/core/index-compound/#prefixes)
-   [MongoDB Docs: Indexing Strategies](https://docs.mongodb.com/manual/applications/indexes/)
-   [MongoDB Docs: Data Modeling Introduction](https://docs.mongodb.com/manual/core/data-modeling-introduction/)
-   [MongoDB University M320: Data Modeling](https://university.mongodb.com/courses/M320/about)
-   [MongoDB University M201: MongoDB Performance](https://university.mongodb.com/courses/M201/about)
-   [Blog Series: Building with Patterns](https://www.mongodb.com/blog/post/building-with-patterns-a-summary)


Don't fall into the trap of this MongoDB Schema Design Anti-Pattern: Unnecessary Indexes

Unnecessary Indexes

We've reached the sixth and final (at least for now) MongoDB schema design anti-pattern. In the first five posts in this series, we've covered the following anti-patterns.

-   [Massive arrays](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays)
-   [Massive number of collections](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-number-collections)
-   [Unnecessary indexes](https://developer.mongodb.com/article/schema-design-anti-pattern-unnecessary-indexes)
-   [Bloated documents](https://developer.mongodb.com/article/schema-design-anti-pattern-bloated-documents)
-   [Separating data that is accessed together](https://developer.mongodb.com/article/schema-design-anti-pattern-separating-data)

Today, we'll explore the wonderful world of case-insensitive indexes.  Not having a case-insensitive index can create surprising query results and/or slow queries...and make you hate everything.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/ihateeverything_cdf41e1fc0.gif"
        alt="Ron Swanson says 'I hate everything'"
    />
</figure>

Once you know the details of how case-insensitive queries work, the implementation is fairly simple. Let's dive in!

>
>
>:youtube[]{vid=mHeP5IbozDU start=948}
>
>Check out the video above to see the case-insensitive queries and indexes in action.
>
>

## Case-Insensitive Queries Without Case-Insensitive Indexes

MongoDB supports three primary ways to run case-insensitive queries.

First, you can run a case-insensitive query using [$regex](https://docs.mongodb.com/manual/reference/operator/query/regex/) with the `i` option. These queries will give you the expected case-insensitive results. However, queries that use `$regex` cannot efficiently utilize case-insensitive indexes, so these queries can be very slow depending on how much data is in your collection.

Second, you can run a case-insensitive query by creating a [case-insensitive index](https://docs.mongodb.com/manual/core/index-case-insensitive/) (meaning it has a [collation strength](https://docs.mongodb.com/manual/reference/collation/) of `1` or `2`) and running a query with the same collation as the index. A [collation](https://docs.mongodb.com/manual/reference/collation/) defines the language-specific rules that MongoDB will use for string comparison. Indexes can optionally have a [collation with a strength that ranges from 1 to 5](https://docs.mongodb.com/manual/reference/collation/). Collation strengths of `1` and `2` both give you case-insensitivity. For more information on the differences in collation strengths, see the [MongoDB docs](https://docs.mongodb.com/manual/reference/collation/). A query that is run with the same collation as a case-insensitive index will return case-insensitive results. Since these queries are [covered by indexes](https://docs.mongodb.com/manual/core/query-optimization/#covered-query), they execute very quickly.

Third, you can run a case-insensitive query by setting the default collation strength for queries and indexes to a strength of `1` or `2` when you create a collection. All queries and indexes in a collection automatically use the default collation unless you specify otherwise when you execute a query or create an index. Therefore, when you set the default collation to a strength of `1` or `2`, you'll get case-insensitive queries and indexes by default. See the `collation` option in the [db.createCollection() section of the MongoDB Docs](https://docs.mongodb.com/manual/reference/method/db.createCollection/#db.createCollection) for more details.

>
>
>Warning for queries that do not use `$regex`: Your index must have a collation strength of `1` or `2` and your query must use the same collation as the index in order for your query to be case-insensitive.
>
>

You can use [MongoDB Compass](https://www.mongodb.com/products/compass) (MongoDB's desktop GUI) or the [MongoDB Shell](https://www.mongodb.com/products/shell) (MongoDB's command-line tool) to test if a query is returning the results you'd expect, see its execution time, and determine if it's using an index.

## Example

Let's revisit the example we saw in the [Unnecessary Indexes Anti-Pattern](https://developer.mongodb.com/article/schema-design-anti-pattern-unnecessary-indexes) and the [Bloated Documents Anti-Pattern](https://developer.mongodb.com/article/schema-design-anti-pattern-bloated-documents) posts. Leslie is creating a website that features inspirational women.  She has created a database with information about 4,700+ inspirational women. Below are three documents in her `InspirationalWomen` collection.

``` none
{
   "_id": ObjectId("5ef20c5c7ff4160ed48d8f83"),
   "first_name": "Harriet",
   "last_name": "Tubman",
   "quote": "I was the conductor of the Underground Railroad for eight years, 
             and I can say what most conductors can't say; I never ran my 
             train off the track and I never lost a passenger"
},
{
   "_id": ObjectId("5ef20c797ff4160ed48d90ea"),
   "first_name": "HARRIET",
   "middle_name": "BEECHER",
   "last_name": "STOWE",
   "quote": "When you get into a tight place and everything goes against you,
             till it seems as though you could not hang on a minute longer, 
             never give  up then, for that is just the place and time that 
             the tide will turn."
},
{
   "_id": ObjectId("5ef20c937ff4160ed48d9201"),
   "first_name": "Bella",
   "last_name": "Abzug",
   "quote": "This woman's place is in the House—the House of Representatives."
}
```

Leslie decides to add a search feature to her website since the website is currently difficult to navigate. She begins implementing her search feature by creating an index on the `first_name` field. Then she starts testing a query that will search for women named "Harriet."

Leslie executes the following query in the MongoDB Shell:

``` sh
db.InspirationalWomen.find({first_name: "Harriet"})
```

She is surprised to only get one document returned since she has two Harriets in her database: Harriet Tubman and Harriet Beecher Stowe. She realizes that Harriet Beecher Stowe's name was input in all uppercase in her database. Her query is case-sensitive, because it is not using a case-insensitive index.

Leslie runs the same query with [.explain("executionStats")](https://docs.mongodb.com/manual/reference/method/db.collection.explain/index.html) to see what is happening.

``` sh
db.InspirationalWomen.find({first_name: "Harriet"}).explain("executionStats")
```

The Shell returns the following output.

``` javascript
{
   "queryPlanner": {
      ...
      "winningPlan": {
         "stage": "FETCH",
         "inputStage": {
            "stage": "IXSCAN",
            "keyPattern": {
               "first_name": 1
            },
            "indexName": "first_name_1",
            ...
            "indexBounds": {
               "first_name": [
                  "[\"Harriet\", \"Harriet\"]"
               ]
            }
         }
      },
      "rejectedPlans": []
   },
   "executionStats": {
      "executionSuccess": true,
      "nReturned": 1,
      "executionTimeMillis": 0,
      "totalKeysExamined": 1,
      "totalDocsExamined": 1,
      "executionStages": {
         ...
         }
      }
   },
   ...
}
```

She can see that the `winningPlan` is using an `IXSCAN` (index scan) with her `first_name_1` index. In the `executionStats`, she can see that only one index key was examined (`executionStats.totalKeysExamined`) and only one document was examined (`executionStats.totalDocsExamined`). For more information on how to interpret the output from `.explain()`, see [Analyze Query Performance](https://docs.mongodb.com/manual/tutorial/analyze-query-plan/index.html).

Leslie opens Compass and sees similar results.

<figure align="center">
   <img
      style="border-radius: 10px"
      src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/compass1_c28e6c4167.png"
      alt="MongoDB Compass shows that the query is examining only one index key, examining only one document, and returning only one document. It also shows that the query used the first_name_1 index."
   />

   <figcaption  style="color: rgb(184, 196, 194); font-size: 12px; line-height: 18px; margin-bottom: 30px;" >MongoDB Compass shows that the query is examining only one index key, examining only one document, and returning only one document. It also shows that the query used the <code>first_name_1</code> index.</figcaption>
</figure>

Leslie wants all Harriets—regardless of what lettercase is used—to be returned in her query. She updates her query to use `$regex` with option `i` to indicate the regular expression should be case-insensitive. She returns to the Shell and runs her new query:

``` sh
db.InspirationalWomen.find({first_name: { $regex: /Harriet/i} })
```

This time she gets the results she expects: documents for both Harriet Tubman and Harriet Beecher Stowe. Leslie is thrilled! She runs the query again with `.explain("executionStats")` to get details on her query execution. Below is what the Shell returns:

``` javascript
{
   "queryPlanner": {
      ...
      "winningPlan": {
         "stage": "FETCH",
         "inputStage": {
            "stage": "IXSCAN",
            "filter": {
               "first_name": {
                  "$regex": "Harriet",
                  "$options": "i"
               }
            },
            "keyPattern": {
               "first_name": 1
            },
            "indexName": "first_name_1",
            ...
            "indexBounds": {
               "first_name": [
                  "[\"\", {})",
                  "[/Harriet/i, /Harriet/i]"
               ]
            }
         }
      },
      "rejectedPlans": []
   },
   "executionStats": {
      "executionSuccess": true,
      "nReturned": 2,
      "executionTimeMillis": 3,
      "totalKeysExamined": 4704,
      "totalDocsExamined": 2,
      "executionStages": {
         ...
      }
   },
   ...
}
```

She can see that this query, like her previous one, uses an index (`IXSCAN`). However, since `$regex` queries cannot efficiently utilize case-insensitive indexes, she isn't getting the typical benefits of a query that is covered by an index. All 4,704 index keys (`executionStats.totalKeysExamined`) are being examined as part of this query, resulting in a slightly slower query (`executionStats.executionTimeMillis: 3`) than one that fully utilizes an index.

She runs the same query in Compass and sees similar results. The query is using her `first_name_1` index but examining every index key.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/compass2_d528279eaa.png"
        alt="MongoDB Compass shows that the query is returning two documents as expected. The $regex query is using the first_name_1 index but examining every index key."
    />

   <figcaption  style="color: rgb(184, 196, 194); font-size: 12px; line-height: 18px; margin-bottom: 30px;">MongoDB Compass shows that the query is returning two documents as expected. The <code>$regex</code> query is using the <code>first_name_1</code> index but examining every index key.</figcaption>
</figure>

Leslie wants to ensure that her search feature runs as quickly as possible. She uses Compass to create a new case-insensitive index named `first_name-case_insensitive`. (She can easily create indexes using other tools as well like the Shell or [MongoDB Atlas](https://www.mongodb.com/cloud/atlas) or even programmatically.) Her index will be on the `first_name` field in ascending order and use a custom collation with a locale of `en` and a strength of `2`. Recall from the [previous section](#case-insensitive-queries-without-case-insensitive-indexes)  that the [collation strength](https://docs.mongodb.com/manual/reference/collation/) must be set to `1` or `2` in order for the index to be case-insensitive.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/compass_create_index_c7ca5aa82d.png"
        alt="Creating a new index in MongoDB Compass with a custom collation that has a locale of en and a strength of 2."
    />

   <figcaption  style="color: rgb(184, 196, 194); font-size: 12px; line-height: 18px; margin-bottom: 30px;">Creating a new index in MongoDB Compass with a custom collation that has a locale of <code>en</code> and a strength of <code>2</code>.</figcaption>
</figure>

Leslie runs a query very similar to her original query in the Shell, but this time she specifies the collation that matches her newly-created index:

``` sh
db.InspirationalWomen.find({first_name: "Harriet"}).collation( { locale: 'en', strength: 2 } )
```

This time she gets both Harriet Tubman and Harriet Beecher Stowe.  Success!

She runs the query with `.explain("executionStats")` to double check that the query is using her index:

``` sh
db.InspirationalWomen.find({first_name: "Harriet"}).collation( { locale: 'en', strength: 2 } ).explain("executionStats")
```

The Shell returns the following results.

``` javascript
{
   "queryPlanner": {
      ...
      "collation": {
         "locale": "en",
         ...
         "strength": 2,
         ...
      },
      "winningPlan": {
         "stage": "FETCH",
         "inputStage": {
            "stage": "IXSCAN",
            "keyPattern": {
               "first_name": 1
            },
            "indexName": "first_name-case_insensitive",
            "collation": {
               "locale": "en",
               ...
               "strength": 2,
               ...
            },
            ...
            "indexBounds": {
               "first_name": [
                  "[\"7)KK91O\u0001\u000b\", \"7)KK91O\u0001\u000b\"]"
               ]
            }
         }
      },
      "rejectedPlans": []
   },
   "executionStats": {
      "executionSuccess": true,
      "nReturned": 2,
      "executionTimeMillis": 0,
      "totalKeysExamined": 2,
      "totalDocsExamined": 2,
      "executionStages": {
         ...
         }
      }
   },
   ...
}
```

Leslie can see that the winning plan is executing an `IXSCAN` (index scan) that uses the case-insensitive index she just created. Two index keys (`executionStats.totalKeysExamined`) are being examined, and two documents (`executionStats.totalDocsExamined`) are being examined. The query is executing in 0 ms (`executionStats.executionTimeMillis: 0`). Now that's fast!

Leslie runs the same query in Compass and specifies the collation the query should use.

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/compass3_3306e3e265.png"
        alt="Query with collation in Compass."
    />
</figure>

She can see that the query is using her case-insensitive index and the
query is executing in 0 ms. She's ready to implement her search feature.
Time to celebrate!

<figure align="center">
    <img
        style="border-radius: 10px"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/anti_patterns_panel_1fddb7c8ef.png"
        alt="Leslie dances."
    />
</figure>

*Note:* Another option for Leslie would have been to set the default collation strength of her InspirationalWomen collection to `1` or `2` when she created her collection. Then all of her queries would have returned the expected, case-insensitive results, regardless of whether she had created an index or not. She would still want to create indexes to increase the performance of her queries.

## Summary

You have three primary options when you want to run a case-insensitive query:

1.  Use `$regex` with the `i` option. Note that this option is not as performant because `$regex` cannot fully utilize case-insensitive indexes.
2.  Create a case-insensitive index with a collation strength of `1` or `2`, and specify that your query uses the same collation.
3.  Set the default collation strength of your collection to `1` or `2` when you create it, and do not specify a different collation in your queries and indexes.

Alternatively, [MongoDB Atlas Search](https://docs.atlas.mongodb.com/atlas-search/) can be used for more complex text searches.

This post is the final anti-pattern we'll cover in this series. But, don't be too sad—this is not the final post in this series. Be on the lookout for the next post where we'll summarize all of the anti-patterns and show you a brand new feature in MongoDB Atlas that will help you discover anti-patterns in your database. You won't want to miss it!

>
>
>When you're ready to build a schema in MongoDB, check out [MongoDB Atlas](http://bit.ly/MDB_Atlas), MongoDB's fully managed database-as-a-service. Atlas is the easiest way to get started with MongoDB and has a generous, forever-free tier.
>
>

## Related Links

Check out the following resources for more information:

-   [MongoDB Docs: Improve Case-Insensitive Regex Queries](https://docs.atlas.mongodb.com/schema-advisor/case-insensitive-regex/)
-   [MongoDB Docs: Case-Insensitive Indexes](https://docs.mongodb.com/manual/core/index-case-insensitive/)
-   [MongoDB Docs: $regex](https://docs.mongodb.com/manual/reference/operator/query/regex/)
-   [MongoDB Docs: Collation](https://docs.mongodb.com/manual/reference/collation/)
-   [MongoDB Docs: db.collection.explain()](https://docs.mongodb.com/manual/reference/method/db.collection.explain/index.html)
-   [MongoDB Docs: Analyze Query Performance](https://docs.mongodb.com/manual/tutorial/analyze-query-plan/index.html)
-   [MongoDB University M201: MongoDB Performance](https://university.mongodb.com/courses/M201/about)



Don't fall into the trap of this MongoDB Schema Design Anti-Pattern: Case-Insensitive Queries Without Case-Insensitive Indexes

Case-Insensitive Queries Without Case-Insensitive Indexes

We're breezing through the MongoDB schema design anti-patterns. So far in this series, we've discussed four of the six anti-patterns:

-   [Massive arrays](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays)
-   [Massive number of collections](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-number-collections)
-   [Unnecessary indexes](https://developer.mongodb.com/article/schema-design-anti-pattern-unnecessary-indexes)
-   [Bloated documents](https://developer.mongodb.com/article/schema-design-anti-pattern-bloated-documents)

[Normalizing data](https://en.wikipedia.org/wiki/Database_normalization) and splitting it into different pieces to optimize for space and reduce data duplication can feel like second nature to those with a relational database background. However, separating data that is frequently accessed together is actually an anti-pattern in MongoDB. In this post, we'll find out why and discuss what you should do instead.


>:youtube[]{vid=dAN76_47WtA t=15}
>
>If you prefer to learn by video (or you just like hearing me repeat, "Data that is accessed together should be stored together"), watch the video above.


## Separating Data That is Accessed Together

Much like you would use a `join` to combine information from different tables in a relational database, MongoDB has a [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#pipe._S_lookup) operation that allows you to join information from more than one [collection](https://docs.mongodb.com/manual/reference/glossary/#term-collection). `$lookup` is great for infrequent, rarely used operations or analytical queries that can run overnight without a time limit. However, `$lookup` is not so great when you're frequently using it in your applications. Why?

`$lookup` operations are slow and resource-intensive compared to operations that don't need to combine data from more than one collection.

The rule of thumb when modeling your data in MongoDB is:

>Data that is accessed together should be stored together.

Instead of separating data that is frequently used together between multiple collections, leverage embedding and arrays to keep the data together in a single collection.

For example, when modeling a [one-to-one relationship](https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-one-relationships-between-documents/#data-modeling-example-one-to-one), you can embed a document from one collection as a subdocument in a document from another. When modeling a [one-to-many relationship](https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/#data-modeling-example-one-to-many), you can embed information from multiple documents in one collection as an array of documents in another.

Keep in mind the other anti-patterns we've already discussed as you begin combining data from different collections together. [Massive, unbounded arrays](https://developer.mongodb.com/article/schema-design-anti-pattern-massive-arrays) and [bloated documents](https://developer.mongodb.com/article/schema-design-anti-pattern-bloated-documents) can both be problematic.

If combining data from separate collections into a single collection will result in massive, unbounded arrays or bloated documents, you may want to keep the collections separate and duplicate some of the data that is used frequently together in both collections. You could use the [Subset Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-subset-pattern) to duplicate a subset of the documents from one collection in another.  You could also use the [Extended Reference Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-extended-reference-pattern) to duplicate a portion of the data in each document from one collection in another. In both patterns, you have the option of creating [references](https://docs.mongodb.com/manual/reference/database-references/) between the documents in both collections. Keep in mind that whenever you need to combine information from both collections, you'll likely need to use `$lookup`. Also, whenever you duplicate data, you are responsible for ensuring the duplicated data stays in sync.

As we have said throughout this series, each use case is different. As you model your schema, carefully consider how you will be querying the data and what the data you will be storing will realistically look like.

## Example

What would an Anti-Pattern post be without an example from Parks and Recreation? I don't even want to think about it. So let's return to Leslie.

Leslie decides to organize a [Model United Nations](https://en.wikipedia.org/wiki/Model_United_Nations) for local high school students and recruits some of her coworkers to participate as well. Each participant will act as a delegate for a country during the event. She assigns Andy and Donna to be delegates for Finland.

Leslie decides to store information related to the Model United Nations in a MongoDB database. She wants to store the following information in her database:

-   Basic stats about each country
-   A list of resources that each country has available to trade
-   A list of delegates for each country
-   Policy statements for each country
-   Information about each Model United Nations event she runs

With this information, she wants to be able to quickly generate the following reports:

-   A country report that contains basic stats, resources currently available to trade, a list of delegates, the names and dates of the last five policy documents, and a list of all of the Model United Nations events in which this country has participated
-   An event report that contains information about the event and the names of the countries who participated

The Model United Nations event begins, and Andy is excited to participate. He decides he doesn't want any of his country's "boring" resources, so he begins trading with other countries in order to acquire all of the world's lions.

<figure align="center">
    <img
        style="border-radius: 10px"
        alt="Parks and Recreation - Andy"
        src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/lions_b7203162ee.gif"
    />
</figure>

Leslie decides to create collections for each of the categories of information she needs to store in her database. After Andy is done trading, Leslie has documents like the following.

``` javascript
//  Countries collection

{
   "_id": "finland",
   "official_name": "Republic of Finland",
   "capital": "Helsinki",
   "languages": [
      "Finnish",
      "Swedish",
      "Sámi"
   ],
   "population": 5528737
}
```

``` javascript
// Resources collection

{
   "_id":  ObjectId("5ef0feeb0d9314ac117d2034"),
   "country_id": "finland",
   "lions": 32563,
   "military_personnel": 0,
   "pulp": 0,
   "paper": 0
}
```

``` javascript
// Delegates collection

{
   "_id": ObjectId("5ef0ff480d9314ac117d2035"),
   "country_id": "finland",
   "first_name": "Andy",
   "last_name": "Fryer"
},
{
   "_id": ObjectId("5ef0ff710d9314ac117d2036"),
   "country_id": "finland",
   "first_name": "Donna",
   "last_name": "Beagle"
}
```

``` javascript
// Policies collection

{
   "_id": ObjectId("5ef34ec43e5f7febbd3ed7fb"),
   "date-created": ISODate("2011-11-09T04:00:00.000+00:00"),
   "status": "draft",
   "title": "Country Defense Policy",
   "country_id": "finland",
   "policy": "Finland has formally decided to use lions in lieu of military for all self defense..."
}
```

``` javascript
// Events collection

{
   "_id": ObjectId("5ef34faa3e5f7febbd3ed7fc"),
   "event-date": ISODate("2011-11-10T05:00:00.000+00:00"),
   "location": "Pawnee High School",
   "countries": [
      "Finland",
      "Denmark",
      "Peru",
      "The Moon"
   ],
   "topic": "Global Food Crisis",
   "award-recipients": [
      "Allison Clifford",
      "Bob Jones"
   ]
}
```

When Leslie wants to generate a report about Finland, she has to use `$lookup` to combine information from all five collections. She wants to optimize her database performance, so she decides to leverage embedding to combine information from her five collections into a single collection.

Leslie begins working on improving her schema incrementally. As she looks at her schema, she realizes that she has a one-to-one relationship between documents in her `Countries` collection and her `Resources` collection. She decides to embed the information from the `Resources` collection as sub-documents in the documents in her `Countries` collection.

Now the document for Finland looks like the following.

``` javascript
//  Countries collection

{
   "_id": "finland",
   "official_name": "Republic of Finland",
   "capital": "Helsinki",
   "languages": [
      "Finnish",
      "Swedish",
      "Sámi"
   ],
   "population": 5528737,
   "resources": {
      "lions": 32563,
      "military_personnel": 0,
      "pulp": 0,
      "paper": 0
   }
}
```

As you can see above, she has kept the information about resources together as a sub-document in her document for Finland. This is an easy way to keep data organized.

She has no need for her `Resources` collection anymore, so she deletes it.

At this point, she can retrieve information about a country and its resources without having to use `$lookup`.

Leslie continues analyzing her schema. She realizes she has a one-to-many relationship between countries and delegates, so she decides to create an array named `delegates` in her `Countries` documents. Each `delegates` array will store objects with delegate information. Now her document for Finland looks like the following:

``` javascript
//  Countries collection

{
   "_id": "finland",
   "official_name": "Republic of Finland",
   "capital": "Helsinki",
   "languages": [
      "Finnish",
      "Swedish",
      "Sámi"
   ],
   "population": 5528737,
   "resources": {
      "lions": 32563,
      "military_personnel": 0,
      "pulp": 0,
      "paper": 0
   },
   "delegates": [
      {
         "first_name": "Andy",
         "last_name": "Fryer"
      },
      {
         "first_name": "Donna",
         "last_name": "Beagle"
      }
   ]
}
```

Leslie feels confident about storing the delegate information in her country documents since each country will have only a handful of delegates (meaning her array won't grow infinitely), and she won't be frequently accessing information about the delegates separately from their associated countries.

Leslie no longer needs her `Delegates` collection, so she deletes it.

Leslie continues optimizing her schema and begins looking at her `Policies` collection. She has a one-to-many relationship between countries and policies. She needs to include the titles and dates of each country's five most recent policy documents in her report. She considers embedding the policy documents in her country documents, but the documents could quickly become quite large based on the length of the policies. She doesn't want to fall into the trap of the [Bloated Documents Anti-Pattern](https://developer.mongodb.com/article/schema-design-anti-pattern-bloated-documents), but she also wants to avoid using `$lookup` every time she runs a report.

Leslie decides to leverage the [Subset Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-subset-pattern). She stores the titles and dates of the five most recent policy documents in her country document. She also creates a reference to the policy document, so she can easily gather all of the information for each policy when needed. She leaves her `Policies` collection as-is. She knows she'll have to maintain some duplicate information between the documents in the `Countries` collection and the `Policies` collection, but she decides duplicating a little bit of information is a good tradeoff to ensure fast queries.

Her document for Finland now looks like the following:

``` javascript
//  Countries collection

{
   "_id": "finland",
   "official_name": "Republic of Finland",
   "capital": "Helsinki",
   "languages": [
      "Finnish",
      "Swedish",
      "Sámi"
   ],
   "population": 5528737,
   "resources": {
      "lions": 32563,
      "military_personnel": 0,
      "pulp": 0,
      "paper": 0
   },
   "delegates": [
      {
         "first_name": "Andy",
         "last_name": "Fryer"
      },
      {
         "first_name": "Donna",
         "last_name": "Beagle"
      }
   ],
   "recent-policies": [
      {
         "_id": ObjectId("5ef34ec43e5f7febbd3ed7fb"),
         "date-created": ISODate("2011-11-09T04:00:00.000+00:00"),
         "title": "Country Defense Policy"
      },
      {
         "_id": ObjectId("5ef357bb3e5f7febbd3ed7fd"),
         "date-created": ISODate("2011-11-10T04:00:00.000+00:00"),
         "title": "Humanitarian Food Policy"
      }
   ]
}
```

Leslie continues examining her query for her report on each country. The last `$lookup` she has combines information from the `Countries` collection and the `Events` collection. She has a many-to-many relationship between countries and events. She needs to be able to quickly generate reports on each event as a whole, so she wants to keep the `Events` collection separate. She decides to use the [Extended Reference Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-extended-reference-pattern) to solve her dilemma. She includes the information she needs about each event in her country documents and maintains a reference to the complete event document, so she can get more information when she needs to. She will duplicate the event date and event topic in both the `Countries` and `Events` collections, but she is comfortable with this as that data is very unlikely to change.

After all of her updates, her document for Finland now looks like the following:

``` javascript
//  Countries collection

{
   "_id": "finland",
   "official_name": "Republic of Finland",
   "capital": "Helsinki",
   "languages": [
      "Finnish",
      "Swedish",
      "Sámi"
   ],
   "population": 5528737,
   "resources": {
      "lions": 32563,
      "military_personnel": 0,
      "pulp": 0,
      "paper": 0
   },
   "delegates": [
      {
         "first_name": "Andy",
         "last_name": "Fryer"
      },
      {
         "first_name": "Donna",
         "last_name": "Beagle"
      }
   ],
   "recent-policies": [
      {
         "policy-id": ObjectId("5ef34ec43e5f7febbd3ed7fb"),
         "date-created": ISODate("2011-11-09T04:00:00.000+00:00"),
         "title": "Country Defense Policy"
      },
      {
         "policy-id": ObjectId("5ef357bb3e5f7febbd3ed7fd"),
         "date-created": ISODate("2011-11-10T04:00:00.000+00:00"),
         "title": "Humanitarian Food Policy"
      }
   ],
   "events": [
      {
         "event-id": ObjectId("5ef34faa3e5f7febbd3ed7fc"),
         "event-date": ISODate("2011-11-10T05:00:00.000+00:00"),
         "topic": "Global Food Crisis"
      },
      {
         "event-id": ObjectId("5ef35ac93e5f7febbd3ed7fe"),
         "event-date": ISODate("2012-02-18T05:00:00.000+00:00"),
         "topic": "Pandemic"
      }
   ]
}
```

## Summary

Data that is accessed together should be stored together. If you'll be frequently reading or updating information together, consider storing the information together using nested documents or arrays. Carefully consider your use case and weigh the benefits and drawbacks of data duplication as you bring data together.

Be on the lookout for a post on the final MongoDB schema design anti-pattern!

>When you're ready to build a schema in MongoDB, check out [MongoDB Atlas](http://bit.ly/MDB_Atlas), MongoDB's fully managed database-as-a-service. Atlas is the easiest way to get started with MongoDB and has a generous, forever-free tier.

## Related Links

Check out the following resources for more information:

-   [MongoDB Docs: Reduce $lookup Operations](https://docs.atlas.mongodb.com/schema-advisor/reduce-lookup-operations/)
-   [MongoDB Docs: Data Model Design](https://docs.mongodb.com/manual/core/data-model-design/)
-   [MongoDB Docs: Model One-to-One Relationships with Embedded Documents](https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-one-relationships-between-documents/)
-   [MongoDB Docs: Model One-to-Many Relationships with Embedded Documents](https://docs.mongodb.com/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/)
-   [MongoDB University M320: Data Modeling](https://university.mongodb.com/courses/M320/about)
-   [Blog Post: The Subset Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-subset-pattern)
-   [Blog Post: The Extended Reference Pattern](https://www.mongodb.com/blog/post/building-with-patterns-the-extended-reference-pattern)
-   [Blog Series: Building with Patterns](https://www.mongodb.com/blog/post/building-with-patterns-a-summary)

Don't fall into the trap of this MongoDB Schema Design Anti-Pattern: Separating Data That is Accessed Together

Separating Data That is Accessed Together

Jan Piotrowski

Prisma ORM (object relational-mapper) [recently released support for MongoDB](https://www.prisma.io/blog/mongodb-general-availability-pixnun6mffmu). This represents the first time Prisma has supported a database outside of the SQL world. Prisma has been known for supporting many relational databases, but how did it end up being able to support the quite different MongoDB?

> 🕴️ I work as the Engineering Manager of Prisma’s Schema team. We are responsible for the schema management parts of Prisma, which most prominently include our migrations and introspection tools, as well as the Prisma schema language and file (and our awesome [Prisma VS Code extension](https://marketplace.visualstudio.com/items?itemName=Prisma.prisma)!).
>
> The other big team working on Prisma object relational-mapper (ORM) is the Client team that builds Prisma Client and the Query Engine. These let users interact with their database to read and manipulate data.
>
> In this blog post, I summarize how our team got Prisma’s schema introspection feature to work for the new MongoDB connector and the interesting challenges we solved along the way.


## Prisma

Prisma is a Node.js database ORM built around the [Prisma schema language and the Prisma schema file](https://www.prisma.io/docs/concepts/components/prisma-schema) containing an abstract representation of a user’s database. When you have tables with columns of certain data types in your database, those are represented as models with fields of a type in your Prisma schema.

Prisma uses that information to generate a fully type-safe [TypeScript/JavaScript Prisma Client](https://www.prisma.io/docs/concepts/components/prisma-client) that makes it easy to interact with the data in your database (meaning it will complain if you try to write a `String` into a `Datetime` field, and make sure you, for example, include information for all the non-nullable columns without a default and similar).

[Prisma Migrate](https://www.prisma.io/docs/concepts/components/prisma-migrate) uses your changes to the Prisma schema to automatically generate the SQL required to migrate your database to reflect that new schema. You don’t need to think about the changes necessary. You just write what you want to achieve, and Prisma then intelligently generates the SQL DDL (Data Definition Language) for that.

For users who want to start using Prisma with their existing database, Prisma has a feature called [Introspection](https://www.prisma.io/docs/concepts/components/introspection). You call the CLI command `prisma db pull` to “pull in” the existing database schema, and Prisma then can create the Prisma schema for you automatically, so your existing database can be used with Prisma in seconds.

This works the same for PostgreSQL, MySQL, MariaDB, SQL Server, CockroachDB, and even SQLite and relies on _relational databases_ being pretty similar, having tables and columns, understanding some dialect of SQL, having foreign keys, and concepts like referential integrity.


## Prisma + MongoDB

One of our most requested features was support for Prisma with MongoDB. The [feature request issue on GitHub for MongoDB support](https://github.com/prisma/prisma/issues/1277) from January 2020 was for a long time by far the most popular one, having gained more than a total of 800 reactions.

![GitHub issue reactions image](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/1_c73f797f9a.png)

MongoDB is known for its flexible schema and the document model, where you can store JSON-like documents. MongoDB takes a different paradigm from relational databases when modeling data – there are no tables, no columns, schemas, or foreign keys to represent relations between tables. Data is often stored grouped in the same document with related data or “denormalized,” which is different from what you would see in a relational database.

So, how could these very different worlds be brought together?


## Prisma and MongoDB: Schema team edition

For our team, this meant figuring out: 

1. How to represent a MongoDB structure and its documents in a Prisma schema.
2. How to migrate said data structures.
3. How to let people introspect their existing MongoDB database to easily be able to start using Prisma.

Fortunately solving 1 and 2 was relatively simple:

1. Where relational databases have tables, columns, and foreign keys that are mapped to Prisma’s models, with their fields and relations, MongoDB has equivalent collections, fields, and [references](https://www.mongodb.com/docs/manual/core/data-modeling-introduction/#references) that could be mapped the same way. Prisma Client can use that information to provide the same type safety and functionality on the Client side.

<table width="100%">
  <tr>
   <td width="33%" align="right">
<strong>Relational database</strong>
   </td>
   <td width="33%" align="center"><strong>Prisma</strong>
   </td>
   <td width="33%" align="left"><strong>MongoDB</strong>
   </td>
  </tr>
  <tr>
   <td align="right">Table →
   </td>
   <td align="center">Model
   </td>
   <td>← Collection
   </td>
  </tr>
  <tr>
   <td align="right">Column →
   </td>
   <td align="center">Field
   </td>
   <td>← Field
   </td>
  </tr>
  <tr>
   <td align="right">Foreign Key →
   </td>
   <td align="center"~~~~>Relation
   </td>
   <td>← Reference
   </td>
  </tr>
</table>

<ol start="2">
<li>With no database-side schema to migrate, creating and updating indexes and constraints was all that was needed for evolving a MongoDB database schema. As there is no SQL to modify the database structure (which is not written down or defined anywhere), Prisma also did not have to create migration files with Data Definition Language (DDL) statements and could just scope it down to allowing `prisma db push` to directly bring the database to the desired end state.</li>
</ol>

A bigger challenge turned out to be the Introspection feature.


## Introspecting a schema with MongoDB

With relational databases with a schema, there is always a way to inquire for the schema. In PostgreSQL, for example, you can query multiple views in a `information_schema` schema to figure out all the details about the structure of the database—to, for example, generate the DDL SQL required to recreate a database, or abstract it into a Prisma schema.

Because MongoDB has a flexible schema (unless schemas are enforced through the schema validation feature), no such information store exists that could be easily queried. That, of course, poses a problem for how to implement introspection for MongoDB in Prisma.

## Research

As any good engineering team would, we started by ... Googling a bit. No need to reinvent the wheel, if someone else solved the problem in the past before. Searches for “MongoDB introspection,” “MongoDB schema reverse engineering,” and (as we learned the native term) “MongoDB infer schema” fortunately brought some interesting and worthwhile results.


### MongoDB Compass

MongoDB’s own database GUI Compass has a [“Schema” tab](https://www.mongodb.com/docs/compass/current/schema/) in a collection that can analyze a collection to “provide an overview of the data type and shape of the fields in a particular collection.”

![MongoDB Compass GUI Schema tab](https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/2_f3f060548e.png)

It works by sampling 1000 documents from a collection that has at least 1000 documents in it, analyzing the individual fields and then presenting them to the user.

### `mongodb-schema`

Another resource we found was Lucas Hrabovsky’s [`mongodb-infer` repository](https://github.com/imlucas/mongodb-infer) from 2014. Later that year, it seemed to have merged/been replaced by [`mongodb-schema`](https://github.com/mongodb-js/mongodb-schema), which is updated to this day.

It’s a CLI and library version of the same idea—and indeed, when checking the source code of MongoDB Compass, you see a dependency for `mongodb-schema` that is used under the hood.


## Implementing introspection for MongoDB in Prisma

Usually, finding an open source library with an Apache 2.0 license means you just saved the engineering team a lot of time, and the team can just become a user of the library. But in this case, we wanted to implement our introspection in the same introspection engine we also use for the SQL databases—and that is written in Rust. As there is no `mongodb-schema` for Rust yet, we had to implement this ourselves. Knowing how `mongodb-schema` works, this turned out to be straightforward:

We start by simply getting all collections in a database. The [MongoDB Rust driver](https://github.com/mongodb/mongo-rust-driver) provides a handy [`db.list_collection_names()`](https://github.com/mongodb/mongo-rust-driver#getting-a-handle-to-a-database) that we can call to get all collections—and each collection is turned into a model for Prisma schema. 🥂

To fill in the fields with their type, we get a sample of up to 1000 random records from each collection and loop through them. For each entry, we note which fields exist, and what data type they have. We map the BSON type to our Prisma scalar types (and native type, if necessary). Optimally, all entries have the same fields with the same data type, which is easily and cleanly mappable—and we are done!

Often, not all entries in a collection are that uniform. Missing fields, for example, are expected and equivalent to `NULL` values in a relational database.


### How to present fields with different types

But different types (for example, `String` and `Datetime`) pose a problem: Which type should we put into the Prisma schema?

> 🎓 **Learning 1: Just choosing the most common data type is not a good idea.**
>
> In an early iteration of MongoDB introspection, we defaulted to the most common type, and left a comment with the percentage of the occurrences in the Prisma schema. The idea was that this should work most of the time and give the developer the best development experience—the better the types in your Prisma schema, the more Prisma can help you.
>
> But we quickly figured out when testing this that there was a slight (but logical) problem: Any time the Prisma Client encounters a type that does _not_ match what it has been told via the Prisma schema, it has to throw an error and abort the query. Otherwise, it would return data that does not adhere to its own generated types for that data.
>
> While we were aware this would happen, it was not intuitive to us _how often_ that would cause the Prisma Client to fail. We quickly learned about this when using such a Prisma schema with conflicting types in the underlying database with Prisma Studio, the built-in database GUI that comes with Prisma CLI (just run `npx prisma studio`). By default, it loads 100 entries of a model you view—and when there were ~5% entries with a different type in a database of 1000 entries, it was very common to hit that on the first page already. Prisma Studio (and also an app using these schemas) was essentially unusable for these data sets this way.

Fortunately, _everything_ in MongoDB is a `Document`, which maps to a `Json` type field in Prisma. So, when a field has different data types, we use `Json` instead, output a warning in Prisma CLI, and put a comment above the field in the Prisma schema that we render, which includes information about the data types we found and how common they were.

<figure>
<img src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/3_fd4367364a.png" alt="Output of Prisma CLI on conflicting data types">
<figcaption align = "center">Output of Prisma CLI on conflicting data types</figcaption>
</figure>

<figure>
<img src="https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/4_8ddf4af2fd.png" alt="Resulting Prisma schema with statistics on conflicting data types">
<figcaption align = "center">Resulting Prisma schema with statistics on conflicting data types</figcaption>
</figure>

### How to iterate on the data to get to a cleaner schema

Using `Json` instead of a specific data type, of course, substantially lowers the benefit you get from Prisma and effectively enables you to write any JSON into the field (making the data even less uniform and harder to handle over time!). But at least you can read all existing data in Prisma Studio or in your app and interact with it.

The preferred way to fix conflicting data types is to read and update them manually with a script, and then run `prisma db pull` again. The new Prisma schema should then show only the one type still present in the collection.

> 🎓 **Learning 2: Output Prisma types in Prisma schema, not MongoDB types.**
>
> Originally, we outputted the raw type information we got from the MongoDB Rust driver, the [BSON types](https://www.mongodb.com/docs/manual/reference/bson-types/), into our CLI warnings and Prisma schema comments to help our users iterate on their data and fix the type. It turned out that while this was technically correct and told the user what type the data was in, using the BSON type names was confusing in a Prisma context. We switched to output the Prisma type names instead and this now feels much more natural to users.

While Prisma recommends everyone to clean up their data and minimize the amount of conflicting types that fall back to `Json`, that is, of course, also a valid choice.


### How to enrich the introspected Prisma schema with relations

By adding relation information to your introspected Prisma schema, you can tell Prisma to handle a specific column like a foreign key and create a relation with the data in it. `user User @relation(fields: [userId], references: [id])` creates a relation to the `User` model via the local `userId` field. So, if you are using MongoDB references to model relations, add `@relation` to them for Prisma to be able to access those in Prisma Client, emulate referential actions, and help with referential integrity to keep data clean.

Right now, Prisma does not offer a way to detect or confirm the potential relations between different collections. We want to learn first how MongoDB users actually use relations, and then help them the optimal way.

## Summary

Implementing a good introspection story for MongoDB was a fun challenge for our team. In the beginning, it felt like two very different worlds were clashing together, but in the end, it was straightforward to find the correct tradeoffs and solutions to get the optimal outcome for our users. We are confident we found a great combination that combines the best of MongoDB with what people want from Prisma.

Try out [Prisma and MongoDB with an existing MongoDB database](https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/mongodb-typescript-mongodb), or [start from scratch and create one along the way](https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/mongodb-typescript-mongodb).

In this blog, you’ll learn about Prisma and how we interact with MongoDB, plus the next steps after having a schema.

Schema

Featured

Schema Suggestions with Julia Oppenheim - Podcast Episode 59

Paginations 2.0: Why I Would Choose MongoDB

How Prisma Introspects a Schema from a MongoDB Database

Schema Articles

Paginations 2.0: Why I Would Choose MongoDB

How Prisma Introspects a Schema from a MongoDB Database

A Summary of Schema Design Anti-Patterns and How to Spot Them

Schema Tutorials

Building with Patterns: The Subset Pattern

Building with Patterns: The Outlier Pattern

Building with Patterns: The Bucket Pattern

All Schema Content