Behind the systems
Requirements, data models, tradeoffs, and reproducible checks.
CASE STUDY 01 · PORTFOLIO APPLICATION
From a simulated queue to saved order operations
The original prototype added sample orders to temporary page state. The expanded app stores orders on the server, validates workflow changes, and keeps a timestamped history. It is a portfolio implementation inspired by retail operations, not a production integration used by Floral Station Deli.
Requirements
- Create an order from POS, Web, or Delivery with an exact monetary value.
- Move orders through received, preparing, ready, and fulfilled states; allow cancellation before fulfillment.
- Retain history and restore the queue after a reload.
- Keep each anonymous visitor’s demo records separate.
Database design
HttpOnly browser cookieRandom UUID · 30-day access1 → 0..many
id · primary keyworkspace · indexed scopereference · channel · centsstatus · created_at · updated_athistory · JSON event list1 → 1..many
Embedded in order.historyat · timestampstatus · workflow stateDecisions and tradeoffs
- Integer cents: avoid floating-point arithmetic when storing money.
- Server validation: reject invalid amounts and skipped or backward workflow transitions.
- Optimistic concurrency: updates require the previously read status, preventing a stale tab from overwriting newer progress.
- Embedded history: update the status and its bounded event history atomically in one row. A separate events table would be better for large-scale audit reporting.
- Anonymous demo scope: no recruiter login is required, but clearing the cookie loses access. This is not account-based authentication or a production customer-data system.
- Capacity: a workspace is limited to 200 orders. Production use would need authentication, retention rules, abuse controls, and operational monitoring.
Testing evidence
The source includes executable validation and SQLite integration checks covering invalid values, lifecycle rules, workspace isolation, durable reloads, and stale updates. These are automated source-level tests; they are not a claim of production load testing or a live POS connection.
Read the recorded test run →CASE STUDY 02 · RELATIONAL DESIGN EXERCISE
University enrollment model
A student can enroll in multiple course sections, and a section can contain multiple students. An enrollment entity resolves that many-to-many relationship. This is an illustrative portfolio model, not a reproduction of the submitted IT 117 assignment.
student_id · PKemail · UNIQUE1 → 0..many
enrollment_id · PKstudent_id · FKsection_id · FKUNIQUE(student_id, section_id)grade · nullable0..many ← 1
section_id · PKcourse_id · FKterm0..many ← 1
course_id · PKtitle · creditsWhy this structure?
Course describes the catalog entry; section describes a specific offering. Enrollment stores the grade because the grade belongs to one student’s attempt in one section, not to the student or course alone. A unique student/section pair prevents duplicate enrollment without preventing a student from retaking a course in a different section.
Validation
The test fixture enables foreign keys, rejects orphan and duplicate enrollments, allows a course retake in a different section, and verifies an enrollment join. The fixture deliberately omits instructors and departments to keep the scope clear.
Download the SQL model →Business result versus demo result
The deli’s 32% sales increase and 18% waste reduction are results reported in my résumé. The order app’s test results demonstrate software behavior; they do not measure or verify those business outcomes.