Browser based CRUD system

For a small business invoicing application, the schema will include tables to manage clients, invoices, invoice items, payments, and products. This schema will cover the core functionality for generating invoices, tracking client information, and handling payments. Here’s a SQL schema with commonly used elements:

-- Client Table
CREATE TABLE Client (
    clientId INT AUTO_INCREMENT PRIMARY KEY,
    clientName VARCHAR(255) NOT NULL,
    contactPerson VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    postalCode VARCHAR(20),
    country VARCHAR(100),
    status ENUM('active', 'inactive') DEFAULT 'active',
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Product Table
CREATE TABLE Product (
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName VARCHAR(255) NOT NULL,
    description TEXT,
    unitPrice DECIMAL(10, 2) NOT NULL,
    stockQuantity INT DEFAULT 0,
    category VARCHAR(100),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Invoice Table
CREATE TABLE Invoice (
    invoiceId INT AUTO_INCREMENT PRIMARY KEY,
    clientId INT NOT NULL,
    invoiceNumber VARCHAR(50) UNIQUE NOT NULL,
    invoiceDate DATE NOT NULL,
    dueDate DATE,
    status ENUM('draft', 'sent', 'paid', 'overdue', 'canceled') DEFAULT 'draft',
    totalAmount DECIMAL(10, 2) DEFAULT 0,
    taxAmount DECIMAL(10, 2) DEFAULT 0,
    notes TEXT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (clientId) REFERENCES Client(clientId)
);

-- InvoiceItem Table
CREATE TABLE InvoiceItem (
    invoiceItemId INT AUTO_INCREMENT PRIMARY KEY,
    invoiceId INT NOT NULL,
    productId INT NOT NULL,
    description TEXT,
    quantity INT NOT NULL DEFAULT 1,
    unitPrice DECIMAL(10, 2) NOT NULL,
    totalAmount DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unitPrice) STORED,
    FOREIGN KEY (invoiceId) REFERENCES Invoice(invoiceId),
    FOREIGN KEY (productId) REFERENCES Product(productId)
);

-- Payment Table
CREATE TABLE Payment (
    paymentId INT AUTO_INCREMENT PRIMARY KEY,
    invoiceId INT NOT NULL,
    paymentDate DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    paymentMethod ENUM('credit_card', 'bank_transfer', 'cash', 'paypal') DEFAULT 'credit_card',
    referenceNumber VARCHAR(50),
    notes TEXT,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (invoiceId) REFERENCES Invoice(invoiceId)
);

-- Audit Table (for tracking changes)
CREATE TABLE Audit (
    auditId INT AUTO_INCREMENT PRIMARY KEY,
    tableName VARCHAR(50) NOT NULL,
    recordId INT NOT NULL,
    action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    actionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    userId INT,
    previousData JSON,
    newData JSON
);

Key Points:

• Client Table: Manages client information, status, and contact details.
• Product Table: Stores product details for line items in invoices.
• Invoice Table: Manages invoice metadata, total amounts, and tax.
• InvoiceItem Table: Tracks individual items on each invoice and calculates line totals.
• Payment Table: Manages payments associated with invoices, supporting multiple payment methods.
• Audit Table: Tracks changes to the main tables, allowing easy auditing of INSERT, UPDATE, and DELETE actions.

This schema will support most common small business invoicing functions while remaining flexible for customization or scaling if needed.