0

I am retrieving a JSON file from an API which size varies between 100kb to 7mb. The structure of the response is:

enter image description here

I initially thought in storing the response in a relational database, with the following tables and fields:

  • operator

    • id
    • name
    • short_name
  • operator_accounts:

    • id
    • operator_id
    • account_type
    • account_name
    • ….
  • operator_records

    • id
    • operator_account_id
    • date
    • text
    • amount
    • type
    • category
  • operator_kpis:

    • id
    • operator_id
    • kpi1
    • kp2

An operator will have between 1 to 20 operator_accounts. An operator_account might have thousands of operator_records. Operator_kpis will always be 1 row per operator.

I am intended to build an application where the users can visualize the operator_records and change/fix some of the rows. The operator_kpis is mainly based/calculated from the operator_records, each time the user change/fix values in the operator_records then the operators_kpis will be updated.

My question is:

I saw lately a video of NoSQL, (I am just getting familiar with them) and now I am confused as I am not sure if I should stay with a relational database like postgres or if I should use something like AWS dynamodb or MongoDB

From my understanding DynamoDB might not work for me due to size limit, but I am not sure if there will be a better way to distribute the JSON in dynamo or use mongo to store the file. Or simple store as S3 and read the information and amend the s3 object if the user made any changes in the records.

3
  • The data to be structured depends upon the way you are going to use it - perform the CRUD operations on it. This is about database design, the relationships between various entities of the concerned data. MongoDB NoSQL allows storing related data in a denormalized form and retrieve it efficiently. Note that, a MongoDB document allows upto 16 MB size data. Refer: Data Modeling Introduction
    – prasad_
    Nov 8, 2021 at 6:14
  • At least for MongoDB the size should be no issue. With sharding MongoDB is able to store Petabytes of data. However, as @prasad_ already noted, the single document must not exceed 16 MiByte unless you use GridFS. It depends how much effort you like to spend to convert JSON to relational data back and forth. Nov 9, 2021 at 13:47
  • In case the document size is an issue, there is always an option to model the data in a normalized form.
    – prasad_
    Nov 9, 2021 at 13:56

1 Answer 1

2

Most goals are accomplishable in either type of database system, but you'll likely find more flexibility in how you can query the data (inclusive of modifying data) with a relational database, at the tradeoff (in your case) of having to convert from JSON into a normalized form.

I think for maintaining data between two objects (operator_records and operator_kpis) you'll find a relational database system easier to utilize, given that your data objects are related already.

1
  • I think query and modifying data in JSON can be as flexible as with relational database, esp. with MongoDB Aggregation Framework. However, if you grow up with SQL and relational databases then you have a rather steep learning curve. Nov 9, 2021 at 13:57

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.