SQL-Datenbankschema für ein Medikamentenverwaltungssystem

Relationales Schema: Medical_Store_System

Ein normalisiertes Design für Inventar, Verkauf, Rezepte und Kundenmanagement.

Entitäts-Beziehungs-Übersicht

• Drugs — Kerninventar
• Drug_Types — Kategorisierung (Analgetikum, Antibiotikum usw.)
• Customers — Patient/Käufer-Informationen
• Prescriptions — verknüpft Kunden, Medikamente, Ärzte
• Invoices — Verkaufstransaktionen
• Invoice_Details — Positionen pro Rechnung

Tabellendefinitionen (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); 

Wichtige Designhinweise

• Drug_Types normalisiert, um Redundanz zu vermeiden.
• Drugs umfasst Ablaufverfolgung und Bestellstand für Inventarwarnungen.
• Prescriptions verknüpft mit Kunde und Medikament; unterstützt Nachfüllverfolgung.
• Invoices/Invoice_Details trennen Kopf/Positionen für normalisierte Verkaufsaufzeichnungen.
• Generierte Spalte Line_Total stellt Berechnungskonsistenz sicher.
• Fügen Sie Trigger/Stored Procedures für Bestandsminderung bei Verkauf, Ablaufwarnungen, Auditprotokolle hinzu.