Relationship Diagrams

Database entity relationships and data flow diagrams for Smart Shelf inventory management system.

Relationship Diagrams

Visual representations of how database entities relate to each other and how data flows through the system.

Core Entity Relationships

The following diagram shows the primary relationships between core entities in the Smart Shelf database:

erDiagram
    users ||--o{ products : creates
    users ||--o{ purchase_orders : creates
    users ||--o{ sales_orders : creates
    
    categories ||--o{ products : contains
    products ||--o{ product_barcodes : has
    products ||--o{ product_variants : has
    products ||--o{ inventory : tracked_in
    products ||--o{ stock_movements : moves
    
    warehouses ||--o{ inventory : stores
    warehouses ||--o{ stock_movements : records
    warehouses ||--o{ warehouse_zones : contains
    warehouse_zones ||--o{ warehouse_locations : contains
    
    suppliers ||--o{ purchase_orders : receives
    purchase_orders ||--o{ purchase_order_items : contains
    
    customers ||--o{ sales_orders : places
    customers ||--o{ invoices : billed_to
    sales_orders ||--o{ sales_order_items : contains
    sales_orders ||--o{ invoices : generates
    
    products ||--o{ purchase_order_items : ordered
    products ||--o{ sales_order_items : sold

Inventory Flow Diagram

This diagram illustrates how inventory moves through the system:

graph TD
    A[Purchase Order] --> B[Stock Movement IN]
    B --> C[Inventory Update]
    D[Sales Order] --> E[Stock Movement OUT]
    E --> C
    F[Adjustment] --> G[Stock Movement ADJUSTMENT]
    G --> C
    H[Transfer] --> I[Stock Movement TRANSFER]
    I --> C

User Access Hierarchy

Shows how user roles relate to data access:

graph TD
    A[Admin] --> B[Full System Access]
    C[Manager] --> D[Department Access]
    E[Employee] --> F[Limited Write Access]
    G[Viewer] --> H[Read Only Access]
    
    B --> I[All Warehouses]
    B --> J[All Products]
    B --> K[All Orders]
    
    D --> L[Assigned Warehouses]
    D --> M[Department Products]
    D --> N[Department Orders]
    
    F --> O[Basic Operations]
    F --> P[Inventory Updates]
    
    H --> Q[View Reports]
    H --> R[View Inventory]

Order Processing Flow

Detailed flow of order processing from creation to fulfillment:

flowchart TD
    A[Sales Order Created] --> B{Inventory Available?}
    B -->|Yes| C[Allocate Inventory]
    B -->|No| D[Backorder]
    C --> E[Generate Pick List]
    E --> F[Pick Items]
    F --> G[Pack Order]
    G --> H[Generate Shipping Label]
    H --> I[Ship Order]
    I --> J[Update Inventory]
    J --> K[Generate Invoice]
    
    D --> L[Purchase Order Created]
    L --> M[Receive Inventory]
    M --> N[Update Stock]
    N --> O[Fulfill Backorder]
    O --> C

Warehouse Structure

Shows the hierarchical structure of warehouse organization:

graph TD
    A[Warehouse] --> B[Zone 1: Storage]
    A --> C[Zone 2: Staging] 
    A --> D[Zone 3: Shipping]
    A --> E[Zone 4: Receiving]
    
    B --> F[Aisle A]
    B --> G[Aisle B]
    F --> H[Shelf 1]
    F --> I[Shelf 2]
    H --> J[Bin A1-1]
    H --> K[Bin A1-2]
    
    C --> L[Staging Area 1]
    C --> M[Staging Area 2]
    
    D --> N[Dock Door 1]
    D --> O[Dock Door 2]
    
    E --> P[Receiving Bay 1]
    E --> Q[Receiving Bay 2]

Financial Data Flow

Tracks how financial transactions flow through the system:

flowchart LR
    A[Purchase Order] --> B[Vendor Invoice]
    B --> C[Expense Record]
    C --> D[Payment]
    
    E[Sales Order] --> F[Customer Invoice]
    F --> G[Payment Received]
    G --> H[Revenue Record]
    
    I[Inventory Adjustment] --> J[Cost Impact]
    J --> K[Financial Adjustment]
    
    L[Stock Movement] --> M[Cost Calculation]
    M --> N[Inventory Valuation]

Audit Trail Structure

Shows how audit information is captured and stored:

graph TD
    A[User Action] --> B[Trigger Fired]
    B --> C[Audit Log Entry]
    C --> D[Old Values Stored]
    C --> E[New Values Stored]
    C --> F[User Context Captured]
    C --> G[Timestamp Recorded]
    
    H[System Action] --> I[Automated Log]
    I --> J[Process Information]
    I --> K[Result Status]
    
    L[Batch Operation] --> M[Batch Audit Log]
    M --> N[Individual Record Logs]
    N --> O[Summary Information]

Integration Points

External system integration touchpoints:

graph LR
    A[Smart Shelf Database] --> B[API Layer]
    B --> C[Frontend Application]
    B --> D[Mobile App]
    B --> E[Third-party Integrations]
    
    E --> F[Accounting System]
    E --> G[Shipping Provider]
    E --> H[Barcode Scanner]
    E --> I[POS System]
    
    J[External Data Sources] --> K[Data Import]
    K --> L[Validation Layer]
    L --> A
    
    A --> M[Data Export]
    M --> N[Reporting Tools]
    M --> O[Business Intelligence]