parse json into a relational database or use a NoSQL?

Question

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

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.

Answer 1

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.