CODEWITHSUNDEEP
databasepostgresqlsoftware-design
developer
developer

Reputation: 1

Database model for blood tests project

I’m developing a medical app (learning purpose) where users can store and manage blood test results, medications, doctor appointments, and medical documents. I’ve designed a PostgreSQL database schema, but I’d love some feedback on best practices, potential pitfalls, and optimizations.

Functional Requirements:

  1. User Authentication: Users can log in using email/password or Google OAuth.

  2. User Profile: Stores name, height, weight, and other personal details.

  3. Medical Documents: Users can upload, view, and organize PDFs/images of blood tests, prescriptions, and other medical reports into folders.

  4. Blood Tests Storage: Users input their blood test values (e.g., TSH, T3, T4, cholesterol, etc.), and the system shows trends & graphs over time.

  5. Medication Tracking: Users can schedule medications with:

    • Start date

    • Dosage (e.g., 2 pills per day)

    • Reminder times (e.g., 08:00 AM, 06:00 PM)

  6. Appointments: Users schedule doctor visits and get notifications.

  7. Notifications System: Sends reminders for medications & doctor visits. Users can snooze reminders (e.g., “remind me in 1 hour”).

enter image description here DB Schema

enter image description here DB RD

To make the question more 'focused', I would only love suggestions how to model the medications table.

I want to be able to take inputs on how many doses the user needs to take, how often, at wich time, when did he start.
Also if the user can not take the medication at the time of the reminder I want the system to make a new reminder an hour later.

Upvotes: -1

Views: 32

Answers (1)

adammak
adammak

Reputation: 78

Try splitting the medication scheduling into two main tables: one for the overall regimen and one for the individual reminders.

The Medication Regimen table captures static, schedule-level details—such as the user ID, medication ID, start time, dose frequency (e.g., “every 8 hours”), and total doses. This table defines the dosing plan without mixing in dynamic event details.

The Medication Reminder table handles each reminder event. It includes a foreign key linking to the regimen table, the scheduled time for the dose, a status field (e.g. pending, completed, missed, postponed), and optionally an actual timestamp for when the dose was taken. When a reminder is missed or the user opts to postpone, your application logic can update the current reminder (or mark it as postponed) and create a new entry with the scheduled time set to one hour later.

This two-table approach separates the static dosing plan from the dynamic reminders. Hope this guides you in your implementation.

Upvotes: 0

Related Questions