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:
- Relational Data
- Tables with rows and columns to identify distinct entities plus database constraints (primary key, foreign key, etc) to enforce data integrity
- Also known as RDBMS
- Also known as database driven development
- JSON Data
- All data will be stored in a single column (or two) in one table
- Also known as NoSQL
- 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!