Designing the database schema for the project

think for some time before seeing this page…..

Database Schema

1. Inventory Items Table:
   • item_id (INT, PRIMARY KEY): Unique identifier for each inventory item.
   • item_name (VARCHAR): Name of the inventory item.
   • item_description (TEXT): Description of the inventory item.
   • quantity (INT): Quantity of the inventory item in stock.
   • price (DECIMAL): Price of the inventory item.
2. Stock Transactions Table:
   • transaction_id (INT, PRIMARY KEY): Unique identifier for each stock transaction.
   • item_id (INT, FOREIGN KEY): References the item_id in the Inventory Items table.
   • transaction_type (ENUM): Type of transaction (e.g., “IN” for incoming, “OUT” for outgoing).
   • quantity (INT): Quantity of items involved in the transaction.
   • transaction_date (DATETIME): Date and time of the transaction.
3. Reports Table:
   • report_id (INT, PRIMARY KEY): Unique identifier for each report.
   • report_name (VARCHAR): Name of the report.
   • report_description (TEXT): Description of the report.
   • report_date (DATETIME): Date and time when the report was generated.
4. Suppliers Table:
   • supplier_id (INT, PRIMARY KEY): Unique identifier for each supplier.
   • supplier_name (VARCHAR): Name of the supplier.
   • supplier_contact (VARCHAR): Contact information of the supplier.

Table Structure

CREATE TABLE IF NOT EXISTS inventory_items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(255),
    item_description TEXT,
    quantity INT,
    price DECIMAL(10, 2)
);

CREATE TABLE IF NOT EXISTS stock_transactions (
    transaction_id INT PRIMARY KEY,
    item_id INT,
    transaction_type ENUM('IN', 'OUT'),
    quantity INT,
    transaction_date DATETIME,
    FOREIGN KEY (item_id) REFERENCES inventory_items(item_id)
);

CREATE TABLE IF NOT EXISTS reports (
    report_id INT PRIMARY KEY,
    report_name VARCHAR(255),
    report_description TEXT,
    report_date DATETIME
);

CREATE TABLE IF NOT EXISTS suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(255),
    supplier_contact VARCHAR(255)
);