Relational Database Design & Data Modeling
Designing structured relational data models across IT operations and healthcare workflows.
This case study demonstrates relational database design across two different operational environments: IT asset management and healthcare medication management. The objective was to translate real-world business rules into structured relational schemas that support data integrity, traceability, and efficient querying.
Project Overview
The project demonstrates how operational requirements can be translated into related entities, keys, and relationship structures. Across both examples, the model uses entity-relationship modeling, primary and foreign key design, many-to-many relationship resolution, associative entities, referential integrity, business-rule translation, relational schema design, and normalization concepts.
IT Asset & Software Management
The first model represents an enterprise environment for tracking employees, computers, software packages, software installations, employee software certifications, and employee skills.
The schema includes entities such as EMPLOYEE, COMPUTER, SOFTWARE PACKAGE, INSTALLEDAT, CERTIFIEDTOUSE, and EMPLOYEESKILL.
Employees can be assigned computers and can also be responsible for maintaining computers. Software packages can be installed across multiple computers, while each computer can contain multiple software packages. The INSTALLEDAT associative entity resolves this many-to-many relationship and also records the installation date.
Employees can be certified to use multiple software packages, and each software package can have multiple certified employees. CERTIFIEDTOUSE resolves this many-to-many relationship. Employee skills are stored separately through EMPLOYEESKILL, allowing multiple skills to be associated with an employee.
This model demonstrates how operational IT information can be decomposed into related entities while minimizing redundancy.
Healthcare Medication Management
The second model applies the same relational modeling principles to a healthcare medication administration environment.
The schema includes PATIENT, DRUG, INGREDIENT, NURSE, DRUGINTAKEEVENT, INCLUDES, CONTAINS, and NOTTOTAKEWITH.
DRUGINTAKEEVENT records medication administration events for patients and connects those events with the nurse responsible for administration. INCLUDES connects individual drug intake events with the drugs administered and records quantity.
CONTAINS models the many-to-many relationship between drugs and ingredients while also storing dosage information. NOTTOTAKEWITH represents drug-to-drug restrictions or interactions using a self-referencing relationship on the DRUG entity.
This design illustrates how relational databases can represent healthcare workflows involving patients, medications, ingredients, administration events, and medication restrictions while maintaining traceability between entities.
Design Approach
- Identify the major business entities.
- Define unique identifiers and primary keys.
- Translate business relationships into foreign-key relationships.
- Resolve many-to-many relationships through associative entities.
- Store relationship-specific attributes in those associative entities.
- Separate repeating or multi-valued information into dedicated tables.
- Structure the schema to reduce redundancy and improve data integrity.
Why This Matters
Database design determines how reliably an organization can store, connect, query, and analyze its operational data. A well-designed relational model provides a strong foundation for SQL analysis, reporting, dashboards, analytics pipelines, and downstream business intelligence.