RDS vs NOSQL

The intent is for openIDL to be serviced and maintained by standard and junior resources. The majority of data oriented developers have worked with ANSI SQL for multiple years; with an eye on staffing a Relational Data Store (RDS) is recommended. A graph database can be loaded from RDS at a later date if relationships and interconnectivity of data elements becomes a strict requirement. ANSI SQL will satisfy all Regulatory Reporting use cases in an affordable and manageable way.

RDS Options

A full list of DBs can be found: https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

Top candidates are Postgres, MariaDB, MySQL

MIN ANSI Requirements

SQL operations cheatsheet, James Madison: http://www.qa76.net/sql_analytics

ANSI HISTORY: https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/#gref

ANSI SQL-92 (MIN): https://en.wikipedia.org/wiki/SQL-92; Advanced Data Types, Case statements, BASIC SQL 

ANSI SQL-2003 (Ideal): https://en.wikipedia.org/wiki/SQL:2003; SQL with window functions

ANSI SQL-2016 (current): https://en.wikipedia.org/wiki/SQL:2016 ; SQL with JSON

Modeling Considerations

DHS will be a multi layer cube optimized for error free loading. Model will also provide business level views to assist with Business Requirments.

This will be archived by utilizing a raw model, and materialized views will be built on top of raw tables for Business Users (BU)s.