2021 – JSON/Relational and SQL/PLSQL

Welcome to 2021 – the year of adaptability! That’s the concept for this years project. Specifically the adaptability of two database design models – relational and JSON – and two programming languages – SQL and PLSQL. This project will work through the life cycle of a database system as it adapts to new requirements. There will be two distinct sets of database structures used:

  1. Relational Data
    1. Tables with rows and columns to identify distinct entities plus database constraints (primary key, foreign key, etc) to enforce data integrity
    2. Also known as RDBMS
    3. Also known as database driven development
  2. JSON Data
    1. All data will be stored in a single column (or two) in one table
    2. Also known as NoSQL
    3. Also known as application driven development

All of the code for the system will reside in an Oracle database and be written in SQL and or PL/SQL because that is how I roll.

The demo application I will use tracks a patient information for a hospital. The initial, very simple requirements of the application are:

Record patient names with associated Medical Record Numbers (MRN’s) across multiple Ordering Systems (OS)

Provide an API to add (not validate) a new patient with a single MRN/OS

Provide an API to retrieve a patients name given an MRN/OS combination

For example if Joe Madeupname gets a Chest X-Ray at Hospital 1 and is assigned an MRN of 123 then the app needs to record the following:

  • Name= Joe Madeupname
  • Ordering System=1
  • MRN=123

Then allow retrieval of Joe Madeupname for OS=1 and MRN=123.

That’s it! No validation of the data…yet. No checking for duplicates…yet.

That’s all they will ever have to do – map patients to MRN’s and be searchable. Seems way to simple to fill a whole year of blog posts?

It is.

To fill a whole year with weekly content I’ll introduce new requirements and demonstrate how the Relational and JSON data structures and SQL and PLSQL code can adapt.

Thanks for reading!

Leave a Comment