SQL Database Schema for a Medical Store Management System
Relational Schema: Medical_Store_System
Normalized design for inventory, sales, prescriptions, and customer management.
Entity-Relationship Overview
- Drugs — core inventory
- Drug_Types — categorization (analgesic, antibiotic, etc.)
- Customers — patient/buyer info
- Prescriptions — links customers, drugs, doctors
- Invoices — sales transactions
- Invoice_Details — line items per invoice
Table Definitions (SQL DDL)
CREATE DATABASE Medical_Store_System;
USE Medical_Store_System;
CREATE TABLE Drug_Types (
Drug_Type_ID INT PRIMARY KEY AUTO_INCREMENT,
Drug_Type_Name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE Drugs (
Drug_ID INT PRIMARY KEY AUTO_INCREMENT,
Drug_Name VARCHAR(150) NOT NULL,
Generic_Name VARCHAR(150),
Dosage_Form VARCHAR(50), -- tablet, capsule, syrup, injection, etc.
Strength VARCHAR(50), -- e.g., '500mg', '10mg/mL'
Drug_Type_ID INT,
Quantity_In_Stock INT DEFAULT 0,
Unit_Price DECIMAL(10,2) NOT NULL,
Reorder_Level INT DEFAULT 10,
Expiry_Date DATE,
Manufacturer VARCHAR(100),
FOREIGN KEY (Drug_Type_ID) REFERENCES Drug_Types(Drug_Type_ID)
);
CREATE TABLE Customers (
Customer_ID INT PRIMARY KEY AUTO_INCREMENT,
Customer_Name VARCHAR(150) NOT NULL,
Phone_Number VARCHAR(20),
Email VARCHAR(100),
Address VARCHAR(255),
City VARCHAR(100),
State VARCHAR(100),
Zip_Code VARCHAR(20),
Date_Registered DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Prescriptions (
Prescription_ID INT PRIMARY KEY AUTO_INCREMENT,
Customer_ID INT NOT NULL,
Drug_ID INT NOT NULL,
Doctor_Name VARCHAR(150),
Doctor_License_No VARCHAR(50),
Prescription_Date DATETIME DEFAULT CURRENT_TIMESTAMP,
Quantity_Prescribed INT NOT NULL,
Dosage_Instructions TEXT,
Refills_Allowed INT DEFAULT 0,
Status ENUM('Active','Filled','Expired','Cancelled') DEFAULT 'Active',
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
FOREIGN KEY (Drug_ID) REFERENCES Drugs(Drug_ID)
);
CREATE TABLE Invoices (
Invoice_ID INT PRIMARY KEY AUTO_INCREMENT,
Customer_ID INT NOT NULL,
Invoice_Date DATETIME DEFAULT CURRENT_TIMESTAMP,
Total_Amount DECIMAL(12,2) NOT NULL,
Payment_Method ENUM('Cash','Card','Insurance','UPI','Other') DEFAULT 'Cash',
Payment_Status ENUM('Paid','Partial','Pending','Refunded') DEFAULT 'Pending',
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);
CREATE TABLE Invoice_Details (
Invoice_Details_ID INT PRIMARY KEY AUTO_INCREMENT,
Invoice_ID INT NOT NULL,
Drug_ID INT NOT NULL,
Quantity INT NOT NULL,
Unit_Price DECIMAL(10,2) NOT NULL,
Line_Total DECIMAL(12,2) GENERATED ALWAYS AS (Quantity * Unit_Price) STORED,
FOREIGN KEY (Invoice_ID) REFERENCES Invoices(Invoice_ID),
FOREIGN KEY (Drug_ID) REFERENCES Drugs(Drug_ID)
);
-- Indexes for performance
CREATE INDEX idx_drugs_name ON Drugs(Drug_Name);
CREATE INDEX idx_prescriptions_customer ON Prescriptions(Customer_ID);
CREATE INDEX idx_invoices_customer_date ON Invoices(Customer_ID, Invoice_Date);
CREATE INDEX idx_invoice_details_invoice ON Invoice_Details(Invoice_ID);
Key Design Notes
- Drug_Types normalized to avoid redundancy.
- Drugs includes expiry tracking and reorder level for inventory alerts.
- Prescriptions link to both customer and drug; supports refill tracking.
- Invoices/Invoice_Details separate header/line-items for normalized sales records.
- Generated Column Line_Total ensures calculation consistency.
- Add triggers/stored procedures for stock decrement on sale, expiry alerts, audit logs.
Healthcare Management - Related Articles
- Using 'Dr.' as a Title: Guidelines & Best Practices
- Oklahoma Cancer Centers: Comprehensive Care & Support
- Medical Record Filing: Best Practices for HIPAA Compliance
- Full Body Massage: What to Expect & Benefits
- J1020 Medical Code: Knee Arthrocentesis (Aspiration/Injection)
- HIPAA Compliance & Medical Records: Federal Laws & Deadlines
- Physician Credentialing: A Comprehensive Guide to Requirements & Processes
