SQL-databasschema för ett apotekshanteringssystem

Relationsschema: Medical_Store_System

En normaliserad design för lagerhantering, försäljning, recept och kundhantering.

Entitets-relationell översikt

• Drugs (Läkemedel) – kärnans lager
• Drug_Types (Läkemedstyper) – kategorisering (analgetika, antibiotika, etc.)
• Customers (Kunder) – patient/köpare information
• Prescriptions (Recept) – kopplar kunder, läkemedel, läkare
• Invoices (Fakturor) – försäljningstransaktioner
• Invoice_Details (Fakturarader) – radobjekt per faktura

Tabelldefinitioner (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, kapsel, sirop, injektion, etc.
    Strength VARCHAR(50), -- t.ex. '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)
);

-- Index för prestanda
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);

Viktiga designanteckningar

• Drug_Types normaliserat för att undvika redundans.
• Drugs inkluderar utgångsdatumsspårning och beställningsnivå för lagervarningar.
• Prescriptions länkar till både kund och läkemedel; stödjer återfyllningsspårning.
• Invoices/Invoice_Details separerar huvud-/radposter för normaliserade försäljningsregister.
• Genererad kolumn Line_Total säkerställer beräkningskonsekvens.
• Lägg till triggers/lagrade procedurer för lageravdrag vid försäljning, utgångsvarningar, revisionsloggar.