Esquema de Banco de Dados SQL para Sistema de Gestão de Farmácia

Esquema Relacional: Medical_Store_System

Um design normalizado para inventário, vendas, prescrições e gerenciamento de clientes.

Visão Geral do Modelo Entidade-Relacionamento

• Drugs — inventário principal
• Drug_Types — categorização (analgésico, antibiótico, etc.)
• Customers — informações do paciente/comprador
• Prescriptions — vincula clientes, medicamentos, médicos
• Invoices — transações de venda
• Invoice_Details — itens da linha por fatura

Definições de Tabelas (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), -- comprimido, cápsula, xarope, injeção, etc. Strength VARCHAR(50), -- 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) ); -- Índices para desempenho 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); 

Notas de Projeto Chave

• Drug_Types normalizado para evitar redundância.
• Drugs inclui rastreamento de validade e nível de reordenação para alertas de inventário.
• Prescriptions vincula tanto o cliente quanto o medicamento; suporta rastreamento de reabastecimento.
• Invoices/Invoice_Details separa cabeçalho/itens de linha para registros de vendas normalizados.
• Coluna Gerada Line_Total garante consistência de cálculo.
• Adicione gatilhos/procedimentos armazenados para decremento de estoque na venda, alertas de validade, logs de auditoria.