Database Design

The Stratpoint Timesheet Application uses a comprehensive relational database design built on MySQL. The database schema is designed to support complex business requirements while maintaining data integrity, performance, and scalability.

Database Schema Overview

Core Entity Relationships

erDiagram
    %% Core User Management
    USERS {
        bigint id PK
        varchar firstname
        varchar lastname
        varchar email UK
        varchar password
        bigint role_id FK
        bigint designation_id FK
        bigint job_family_id FK
        bigint approverUser_id FK
        boolean isActive
        boolean isAnApprover
        decimal currentHourlyCost
        varchar netsuiteId
        timestamp created_at
        timestamp updated_at
    }

    ROLES {
        bigint id PK
        varchar name UK
        varchar description
        boolean isActive
        timestamp created_at
    }

    DESIGNATIONS {
        bigint id PK
        varchar name UK
        varchar description
        boolean isActive
        timestamp created_at
    }

    JOB_FAMILIES {
        bigint id PK
        varchar name UK
        varchar description
        boolean isActive
        timestamp created_at
    }

    %% Project Management
    PROJECTS {
        bigint id PK
        varchar name
        varchar description
        bigint client_id FK
        bigint project_status_id FK
        bigint project_type_id FK
        date startDate
        date endDate
        decimal budget
        varchar netsuiteId
        boolean isActive
        timestamp created_at
        timestamp updated_at
    }

    CLIENTS {
        bigint id PK
        varchar name UK
        varchar description
        varchar contactEmail
        varchar contactPhone
        boolean isActive
        timestamp created_at
    }

    PROJECT_STATUS {
        bigint id PK
        varchar name UK
        varchar description
        varchar color
        boolean isActive
    }

    PROJECT_TYPES {
        bigint id PK
        varchar name UK
        varchar description
        boolean isActive
    }

    PROJECT_USERS {
        bigint id PK
        bigint project_id FK
        bigint user_id FK
        date startDate
        date endDate
        decimal hourlyRate
        boolean isActive
        timestamp created_at
    }

    %% Time Tracking
    TIMELOGS {
        bigint id PK
        bigint user_id FK
        bigint project_user_id FK
        bigint tasktype_id FK
        date logDate
        decimal hours
        varchar description
        varchar location
        boolean isApproved
        bigint approvedBy FK
        timestamp approvedAt
        timestamp created_at
        timestamp updated_at
    }

    TASKTYPES {
        bigint id PK
        varchar name UK
        varchar description
        varchar category
        boolean isActive
        timestamp created_at
    }

    %% Leave Management
    LEAVES {
        bigint id PK
        bigint user_id FK
        bigint leave_type_id FK
        date startDate
        date endDate
        decimal days
        varchar reason
        varchar status
        bigint approvedBy FK
        timestamp approvedAt
        timestamp created_at
        timestamp updated_at
    }

    LEAVE_TYPES {
        bigint id PK
        varchar name UK
        varchar description
        decimal maxDays
        boolean requiresApproval
        boolean isActive
        timestamp created_at
    }

    %% Authorization
    PERMISSIONS {
        bigint id PK
        varchar name UK
        varchar description
        varchar module
        varchar action
        timestamp created_at
    }

    ROLE_PERMISSIONS {
        bigint id PK
        bigint role_id FK
        bigint permission_id FK
        timestamp created_at
    }

    %% Relationships
    USERS ||--o{ TIMELOGS : "creates"
    USERS ||--o{ PROJECT_USERS : "assigned_to"
    USERS ||--o{ LEAVES : "requests"
    USERS }|--|| ROLES : "has_role"
    USERS }|--|| DESIGNATIONS : "has_designation"
    USERS }|--|| JOB_FAMILIES : "belongs_to"
    USERS ||--o{ USERS : "approves"

    PROJECTS ||--o{ PROJECT_USERS : "contains"
    PROJECTS }|--|| CLIENTS : "belongs_to"
    PROJECTS }|--|| PROJECT_STATUS : "has_status"
    PROJECTS }|--|| PROJECT_TYPES : "has_type"

    PROJECT_USERS ||--o{ TIMELOGS : "tracks_time"

    TIMELOGS }|--|| TASKTYPES : "categorized_by"
    TIMELOGS }|--|| USERS : "approved_by"

    LEAVES }|--|| LEAVE_TYPES : "has_type"
    LEAVES }|--|| USERS : "approved_by"

    ROLES ||--o{ ROLE_PERMISSIONS : "has"
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : "granted_to"

Core Tables

Users Table

The central table for all system users and employees:

CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    firstname VARCHAR(255) NOT NULL,
    midname VARCHAR(255),
    lastname VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255),
    position_id BIGINT UNSIGNED,
    designation_id BIGINT UNSIGNED,
    job_family_id BIGINT UNSIGNED,
    role_id BIGINT UNSIGNED NOT NULL,
    companyBooking_id BIGINT UNSIGNED,
    startDate DATE,
    endDate DATE,
    regularizationDate DATE,
    employmentType_id BIGINT UNSIGNED,
    businessUnit_id BIGINT UNSIGNED,
    businessLine_id BIGINT UNSIGNED,
    approverUser_id BIGINT UNSIGNED,
    isAnApprover BOOLEAN DEFAULT FALSE,
    isActive BOOLEAN DEFAULT TRUE,
    isCadet BOOLEAN DEFAULT FALSE,
    isSeniorLeader BOOLEAN DEFAULT FALSE,
    currentHourlyCost DECIMAL(10,2),
    hourlyCostEffectiveDate DATE,
    cardNumber VARCHAR(50),
    netsuiteId VARCHAR(100),
    netsuiteBranch_id BIGINT UNSIGNED,
    province_id BIGINT UNSIGNED,
    municipality_id BIGINT UNSIGNED,
    capability_id BIGINT UNSIGNED,
    techMentorUserId BIGINT UNSIGNED,
    userPriority_id BIGINT UNSIGNED,
    defaultTimelogLocation VARCHAR(255),
    permission_ids TEXT,
    plainTags TEXT,
    comments TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_users_email (email),
    INDEX idx_users_active (isActive),
    INDEX idx_users_role (role_id),
    INDEX idx_users_approver (approverUser_id),
    INDEX idx_users_enddate (endDate),
    INDEX idx_users_cardnumber (cardNumber)
);

Projects Table

Central table for project management:

CREATE TABLE projects (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    companyBooking_id BIGINT UNSIGNED,
    projectStatus_id BIGINT UNSIGNED NOT NULL,
    projectType_id BIGINT UNSIGNED NOT NULL,
    businessUnit_id BIGINT UNSIGNED,
    businessLine_id BIGINT UNSIGNED,
    ownerUser_id BIGINT UNSIGNED,
    salesUser_id BIGINT UNSIGNED,
    startDate DATE,
    endDate DATE,
    actualStartDate DATE,
    actualEndDate DATE,
    oldEndDate DATE,
    pricegroup_id BIGINT UNSIGNED,
    hexcolor_id BIGINT UNSIGNED,
    sowType_id BIGINT UNSIGNED,
    currency_id BIGINT UNSIGNED,
    netsuiteBranch_id BIGINT UNSIGNED,
    netSuiteMainId VARCHAR(100),
    netsuiteCustomer_id BIGINT UNSIGNED,
    parentProjectId BIGINT UNSIGNED,
    dealId BIGINT UNSIGNED,
    initialContractValue DECIMAL(15,2),
    discountPercent DECIMAL(5,2) DEFAULT 0,
    po_number VARCHAR(255),
    uses_foreign_currency BOOLEAN DEFAULT FALSE,
    foreign_currency VARCHAR(10),
    exchange_rate DECIMAL(10,4),
    hasSOW BOOLEAN DEFAULT FALSE,
    sowValidity DATE,
    hasSignedProposal BOOLEAN DEFAULT FALSE,
    is_coca_required BOOLEAN DEFAULT FALSE,
    isTemplateOnly BOOLEAN DEFAULT FALSE,
    isStaffAug BOOLEAN DEFAULT FALSE,
    isForTraining BOOLEAN DEFAULT FALSE,
    includeInMaDashboard BOOLEAN DEFAULT FALSE,
    treatAsMaInPmDashboard BOOLEAN DEFAULT FALSE,
    paymentMilestoneLastComputed TIMESTAMP NULL,
    notes TEXT,
    plainTags TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_projects_client (client_id),
    INDEX idx_projects_status (projectStatus_id),
    INDEX idx_projects_owner (ownerUser_id),
    INDEX idx_projects_dates (startDate, endDate),
    INDEX idx_projects_netsuite (netSuiteMainId)
);

Timelogs Table

Core table for time tracking entries:

CREATE TABLE timelogs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    project_user_id BIGINT UNSIGNED,
    tasktype_id BIGINT UNSIGNED NOT NULL,
    subTaskTypeId BIGINT UNSIGNED,
    subProjectId BIGINT UNSIGNED,
    skillSetId BIGINT UNSIGNED,
    approverUser_id BIGINT UNSIGNED,
    startTime DATETIME NOT NULL,
    stopTime DATETIME NOT NULL,
    spentHours DECIMAL(8,2) NOT NULL,
    description TEXT NOT NULL,
    ticketNumber VARCHAR(255),
    location VARCHAR(255),
    status ENUM('pending', 'approved', 'rejected', 'closed') DEFAULT 'pending',
    statusNote TEXT,
    rejectReasonType VARCHAR(255),
    dateStatusChange TIMESTAMP NULL,
    statusChangedByUser_id BIGINT UNSIGNED,
    statusLog TEXT,
    changedByUserName VARCHAR(255),
    dateMadeUneditable TIMESTAMP NULL,
    toBeUsedForOffset BOOLEAN DEFAULT FALSE,
    availableHoursForOffset DECIMAL(8,2) DEFAULT 0,
    availableHoursForOffsetFromCarryOver DECIMAL(8,2) DEFAULT 0,
    dateFirstUsedAsOffset DATE,
    parentOffsetTimelog_id BIGINT UNSIGNED,
    offsetReason TEXT,
    offsetReasonType VARCHAR(255),
    offsetReasonOthers TEXT,
    ctoExpiryDate DATE,
    ctoExpiredHours DECIMAL(8,2) DEFAULT 0,
    balanceAfterLeaveAdjustment DECIMAL(8,2),
    carryOver DECIMAL(8,2) DEFAULT 0,
    convertToCash DECIMAL(8,2) DEFAULT 0,
    forfeit DECIMAL(8,2) DEFAULT 0,
    isIrSr BOOLEAN DEFAULT FALSE,
    hasNightShift BOOLEAN DEFAULT FALSE,
    nightShiftAmMinutes INT DEFAULT 0,
    nightShiftPmMinutes INT DEFAULT 0,
    plainTags TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_timelogs_user_date (user_id, startTime),
    INDEX idx_timelogs_project_user (project_user_id),
    INDEX idx_timelogs_status (status),
    INDEX idx_timelogs_approver (approverUser_id),
    INDEX idx_timelogs_parent_offset (parentOffsetTimelog_id),
    INDEX idx_timelogs_tasktype (tasktype_id),
    INDEX idx_timelogs_dates (startTime, stopTime)
);

Supporting Tables

Project Users Table

Links users to projects with assignment details:

CREATE TABLE project_users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    project_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    position_id BIGINT UNSIGNED,
    projectPhase_id BIGINT UNSIGNED,
    utilizationStatus_id BIGINT UNSIGNED,
    startDate DATE,
    endDate DATE,
    percentAllocation DECIMAL(5,2) DEFAULT 100,
    budgetedHours DECIMAL(8,2),
    bufferedHours DECIMAL(8,2),
    spentHours DECIMAL(8,2) DEFAULT 0,
    initialSpentHours DECIMAL(8,2) DEFAULT 0,
    hourlyCost DECIMAL(10,2),
    hourlyRate DECIMAL(10,2),
    hourlySalary TEXT, -- Encrypted
    costMarkup DECIMAL(5,2),
    reviewStatus VARCHAR(50),
    reviewNote TEXT,
    isScrumMaster BOOLEAN DEFAULT FALSE,
    canLogMin1HourCTO BOOLEAN DEFAULT FALSE,
    commitmentType VARCHAR(50),
    plainTags TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    UNIQUE KEY unique_project_user (project_id, user_id),
    INDEX idx_project_users_project (project_id),
    INDEX idx_project_users_user (user_id),
    INDEX idx_project_users_dates (startDate, endDate)
);

Leave Management Tables

Leave Availability Table:

CREATE TABLE leave_availability (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    year YEAR NOT NULL,
    taskType_id BIGINT UNSIGNED NOT NULL,
    amountInHours DECIMAL(8,2) NOT NULL,
    amountInDays DECIMAL(6,2) NOT NULL,
    awardedAmountInDays DECIMAL(6,2) DEFAULT 0,
    lastProcessedDate DATE,
    logs JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_user_year_tasktype (user_id, year, taskType_id),
    INDEX idx_leave_availability_user_year (user_id, year),
    INDEX idx_leave_availability_tasktype (taskType_id)
);

Leave Adjustments Table:

CREATE TABLE leave_adjustments (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    year YEAR NOT NULL,
    taskType_id BIGINT UNSIGNED NOT NULL,
    userEmployment_id BIGINT UNSIGNED,
    startDate DATE NOT NULL,
    endDate DATE NOT NULL,
    amountInHours DECIMAL(8,2) NOT NULL,
    amountInDays DECIMAL(6,2) NOT NULL,
    includeWeekends BOOLEAN DEFAULT FALSE,
    logs JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_leave_adjustments_user_year (user_id, year),
    INDEX idx_leave_adjustments_dates (startDate, endDate)
);

Financial Tables

Payment Milestones Table

CREATE TABLE payment_milestones (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    project_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    amount DECIMAL(15,2) NOT NULL,
    grossBilledAmountPhp DECIMAL(15,2),
    netBilledAmount DECIMAL(15,2),
    discountAmount DECIMAL(15,2) DEFAULT 0,
    discountForex DECIMAL(10,4) DEFAULT 1,
    earlyDiscountAmount DECIMAL(15,2) DEFAULT 0,
    interestAmount DECIMAL(15,2) DEFAULT 0,
    dueDate DATE,
    billedDate DATE,
    paidDate DATE,
    collectionStatus_id BIGINT UNSIGNED,
    latestCollectionNotice_id BIGINT UNSIGNED,
    paymentMilestoneStatus_id BIGINT UNSIGNED NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_payment_milestones_project (project_id),
    INDEX idx_payment_milestones_status (paymentMilestoneStatus_id),
    INDEX idx_payment_milestones_dates (dueDate, billedDate, paidDate)
);

Revenue Tracking Tables

CREATE TABLE revenues (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    project_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    year YEAR NOT NULL,
    month TINYINT NOT NULL,
    spentHours DECIMAL(8,2) NOT NULL,
    hourlyRate DECIMAL(10,2) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    forex DECIMAL(10,4) DEFAULT 1,
    adjustmentDifference DECIMAL(15,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_revenue_entry (project_id, user_id, year, month),
    INDEX idx_revenues_project_period (project_id, year, month),
    INDEX idx_revenues_user_period (user_id, year, month)
);

Lookup and Reference Tables

Task Types Table

CREATE TABLE tasktypes (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    isOffsetable BOOLEAN DEFAULT FALSE,
    isLeaveType BOOLEAN DEFAULT FALSE,
    hexcolor_id BIGINT UNSIGNED,
    isActive BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,

    INDEX idx_tasktypes_active (isActive),
    INDEX idx_tasktypes_leave (isLeaveType)
);

Roles and Permissions

CREATE TABLE roles (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    permission_ids TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE permissions (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    module VARCHAR(100),
    prerequisitePermission_ids TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Database Optimization

Indexing Strategy

Primary Indexes: - All tables have auto-incrementing primary keys - Unique constraints on business-critical combinations

Performance Indexes: - Date range queries (startTime, endTime) - Foreign key relationships - Status and active flags - User and project associations

Composite Indexes: - Multi-column indexes for common query patterns - Covering indexes for frequently accessed data

Data Integrity

Foreign Key Constraints: - Referential integrity between related tables - Cascade delete where appropriate - Restrict delete for critical relationships

Check Constraints: - Data validation at database level - Enum values for status fields - Range checks for numeric values

Performance Considerations

Partitioning: - Time-based partitioning for large tables (timelogs, revenues) - Archive old data to maintain performance

Replication: - Read replicas for reporting queries - Master-slave configuration for high availability

Caching: - Query result caching - Application-level caching for frequently accessed data

This database design provides a robust foundation for the Stratpoint Timesheet Application, supporting complex business requirements while maintaining data integrity and performance.