Mastering Database Management

0 of 78 lessons complete (0%)

Project: Inventory Managment

Designing the database schema for the project

You don’t have access to this lesson

Please register or sign in to access the course content.

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