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