2

I have a money management tool which has users. Each user may have several accounts/funds (e.g. bank account). For each account/fund there may be thousands of transactions.

Users may only view their accounts and transactions so no need for cross reference between different users like other apps (e.g. message board, where different users can view other peoples posts)

So, using a nosql database like mongodb, I would just create a single collection "users" and have the following schema:

    {
        "id": ...
        "username": "joe"
        "password": "jkj23jk343krw..."
        "funds": {
            "name": "Bank of Scotland"
            "transactions": [
                {...}, {...}, ...
            ]
        },
        {
            "name": "Lloyds"
            "transactions": [
                {...}, {...}, ...
            ]
        },
    }

Rather than having different collections (users, funds, transactions) as I might have tables in a Relational DB.

2
  • do not use community edition of Mongo for this. The information stored is incredible sensitive and you will want to encrypt it. Encryption at rest is only supported in Mongo Enterprise docs.mongodb.com/manual/core/security-encryption-at-rest
    – null
    Aug 30, 2020 at 17:15
  • I'm curious that with which approach you have moved on and what was the pros and cons you see afterwards. Please share. Jan 23, 2022 at 3:23

3 Answers 3

1

Sounds cool. Just please one-way hash the passwords. People reuse passwords among services and a data leakage can harm everyone.

Or if you make users, funds and transactions separate tables, you'll be able to run queries such as

select user_id, trans_id form transactions
where date >= today - 10
and bank like "Lloyds"

So I vote for separate tables.

1

Multiple collections are better - but if you want to minimize collections then at least you should have two - one for users and another for transactions.

Additionally, instead of nesting information you should have flat documents with repeated information. Like this:

"id": ...
"user_id": ...
"username": "joe"
"bankname": "Bank of Scotland"
"transaction_type":credit
"transaction_amount": 
"date": ... and so on

This will make later queries and aggregations (for finding out account balances etc. ) easier.

-1

One thing the other answers overlooked is the ability to change. If you would make one big nested document you will set yourself up for disaster. Your data will be much harder to handle once the design starts to change. And believe me, designs change all the time.

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