Mongodb – Why can’t I take RDBMS Database Design practices, and use for NoSQL MongoDB Database Designs

database-designmongodbnosqlrdbmsschema

Let's say I have a BankAccount table, and a BankAccountHistoryTransactions table.

When it comes to RDBMS database schema design, most Database designers would recommend something like the following: 

BankAccount Table

int: BankAccountNumber Primary key

double: CashBalance

......

..

Also, in an RDBMS Database Design, the BankAccountHistoryTransactions Table would be like:

BankAccountHistoryTransactions Table

int: BankAccountHistoryTransactionsId Primary key

int: FK_BankAccountNumber Foreign key

DateTime2: DateOfTransaction

.................

.........

In NoSQL MongoDB database schema, it would b more like a BankAccount collection containing an embedded BankAccountHistoryTransactions collection:

db.BankAccount.find().pretty()
{
    "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
    "BankAccountNumber" : "7575785885859",
    "CashBalance" : "890399",
....................................,
...............................,
.......................,
    "BankAccountHistoryTransactions" : {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "BankAccountHistoryTransactionsId": 1,
        "DateOfTransaction" : ISODate("2019-12-31T23:00:00Z")

}
}

My problem with the NoSQL MongoDB Database Schema design approach is that a Bank Account could have a tonne of BankAccountHistoryTransactions entries (possible going into hundred thousands of BankAccountHistoryTransactions entries for a Bank Account).

Therefore, wouldn't it be better if we used a pseudo-foreign key relationship like in the following:

    db.BankAccount.find().pretty()
    {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "BankAccountNumber" : "7575785885859",
        "CashBalance" : "890399",
    ....................................,
    ...............................,
    .......................,
}

And a different separate collection for BankAccountHistoryTransactions 

db.BankAccountHistoryTransactions.find().pretty()
    {
        "_id" : ObjectId("51f7be1cd6189a56c399d3bf"),
        "FK_BankAccountNumber" : "7575785885859",
"BankAccountHistoryTransactionsId": 1,
            "DateOfTransaction" : ISODate("2019-12-31T23:00:00Z")

    }

I've heard that NoSQL MongoDB database designers Discourage the use of pseudo-foreign key relationships like the one above. However, wouldn't it be much more organized, and modular to have a like the "pseudo-foreign key relationship" design? ( Correct me if I'm wrong but Performance might be a problem, but it's certainly more organized and modular )

Best Answer

I wouldn't say that it's "Discourage" to use foreign key references. Normally you use ObjectId value as the reference.

You can "join" those transactions and bank account with aggregation $lookup function.

Here is 6 Rules of Thumb for MongoDB Schema Design. How to design 1-to-many relationships

This article series should answer to most of your concerns in this question.

Related Question