I need to model a database to manage the life cycle of a contract.
Given the following data model I have constructed (which for simplicity of representation is already relational), I need to decide whether to opt for a SQL or NOSQL database.
In addition to the data modeling, I add the following constraints and features:
- the ExtractionEntities table contains entities extracted with a NER (such as People, Places, etc.)
- the “text” field in the Contract table contains all text, so it is quite large
- the database will serve an Apache Superset dashboard. So e.g. it will need to show actors with multiple active contracts, geographic distribution of contracts, filter records based on specific value of a field, etc.
- We will not have a lot of data (<5k for sure)
Given the premise, I would be leaning towards a relational db:
- Relationships between entities look perfectly relational db to me
- It supports referential integrity
- An application that needs to manage the lifecycle of a contract should support ACID Transactions, which is guaranteed by db sql
- The dashboard might require fairly complex queries, which is another point in favor of relational db, isn’t it?
- The logical schema should not change over time (which could have been a pro for NOSQL). Perhaps some records might not always have all the fields
It is clearly have a favorite. I would like to understand if I am making wrong assumptions, or simply can any of you make me see the situation from another perspective to lean toward a different solution.
Thank you