2

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 )

  • I suggest you read this. Primary objective of designing database is efficient retrieval of adhoc and planned data retrieval, data integrity, support business rules and many more. Organizing data for optimizing those objective has high precedence then organizing where it will hurt those objective should be avoided. – SqlWorldWide Aug 11 '17 at 19:15
0

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.

  • Why did someone downvote this answer? Please give a reason. – crazyTech Oct 17 '17 at 20:45
5

If you want to use a relational structure, and you're thinking about doing it like this with a NoSQL system, use an RDBMS, that's what they're designed to do.

Using the right tool for the job will always result in better performance, and no-one has ever been fired for using an RDBMS where one fits instead of using a NoSQL solution where it doesn't make sense, like in a banking situation where you need 100% guaranteed consistency, not "eventual consistency".

Using relational algebra in a non-relational database will just make you replicate relational functionality in code, and I can guarantee you're not better at that than, say, SQL Server or Oracle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.