Database Design - ERP Logistics Equipment Management

Schema Overview

Database Tables

Performance Indexes

Oracle Sequences

Update Triggers

Oracle Optimized

Native Oracle data types, sequences, and PL/SQL triggers for optimal performance.

Referential Integrity

Comprehensive foreign key relationships ensuring data consistency across all entities.

Data Validation

CHECK constraints and data validation rules enforcing business logic at the database level.

ERP Integration

Seamless integration with existing ERP tables through foreign key relationships.

Database Tables

Authentication

Core Entities

Workflow

Financial

Inspection

Authentication & User Management

1. User Profiles

Stores user profile information with role-based access control integration.

CREATE TABLE USER_PROFILES (
    ID VARCHAR2(36) PRIMARY KEY,
    FULL_NAME VARCHAR2(255) NOT NULL,
    ROLE VARCHAR2(50) NOT NULL CHECK (ROLE IN ('PL', 'PMO', 'Operations Manager', 'BOD', 'Construction Manager', 'Coordinator')),
    COUNTRY VARCHAR2(50),
    PHONE VARCHAR2(50),
    DEPARTMENT VARCHAR2(100),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Spring Security Users

Spring Security compatible user authentication table with account status flags.

CREATE TABLE USERS (
    ID VARCHAR2(36) PRIMARY KEY,
    USERNAME VARCHAR2(100) UNIQUE NOT NULL,
    PASSWORD VARCHAR2(255) NOT NULL,
    EMAIL VARCHAR2(255) UNIQUE NOT NULL,
    ENABLED NUMBER(1) DEFAULT 1,
    ACCOUNT_NON_EXPIRED NUMBER(1) DEFAULT 1,
    ACCOUNT_NON_LOCKED NUMBER(1) DEFAULT 1,
    CREDENTIALS_NON_EXPIRED NUMBER(1) DEFAULT 1,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. User Roles

Many-to-many relationship table for user role assignments.

CREATE TABLE USER_ROLES (
    USER_ID VARCHAR2(36) NOT NULL,
    ROLE VARCHAR2(50) NOT NULL,
    CONSTRAINT FK_USER_ROLES_USER FOREIGN KEY (USER_ID) REFERENCES USERS(ID)
);

Core Business Entities

4. Projects

Project master data with contract information and multi-country support.

CREATE TABLE PROJECTS (
    ID VARCHAR2(50) PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    CLIENT VARCHAR2(255) NOT NULL,
    LOCATION VARCHAR2(255) NOT NULL,
    COUNTRY VARCHAR2(50) NOT NULL CHECK (COUNTRY IN ('Libya', 'Tunisia', 'Iraq')),
    START_DATE DATE NOT NULL,
    END_DATE DATE,
    STATUS VARCHAR2(20) DEFAULT 'active' CHECK (STATUS IN ('active', 'completed', 'on_hold', 'cancelled')),
    DESCRIPTION CLOB,
    BUDGET NUMBER(15,2),
    CONTRACT_REFERENCE VARCHAR2(100),
    COST_CENTER VARCHAR2(50),
    PROJECT_MANAGER VARCHAR2(255),
    CONTRACT_TYPE VARCHAR2(50),
    CONTRACT_VALUE NUMBER(15,2),
    CONTRACT_CURRENCY VARCHAR2(10) DEFAULT 'USD',
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5. Equipment

Comprehensive equipment management with ownership types, status tracking, and maintenance scheduling.

CREATE TABLE EQUIPMENT (
    ID VARCHAR2(50) PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    TYPE VARCHAR2(100) NOT NULL,
    MODEL VARCHAR2(100) NOT NULL,
    SERIAL_NUMBER VARCHAR2(100) NOT NULL UNIQUE,
    OWNERSHIP VARCHAR2(20) NOT NULL CHECK (OWNERSHIP IN ('owned', 'rented')),
    STATUS VARCHAR2(20) DEFAULT 'idle' CHECK (STATUS IN ('active', 'maintenance', 'idle', 'out_of_service')),
    DAILY_RATE NUMBER(10,2),
    CURRENCY VARCHAR2(10) DEFAULT 'USD',
    PURCHASE_DATE DATE,
    RENTAL_START_DATE DATE,
    RENTAL_END_DATE DATE,
    LAST_MAINTENANCE_DATE DATE,
    NEXT_MAINTENANCE_DATE DATE,
    PROJECT_ID VARCHAR2(50),
    SUBCONTRACTOR_ID VARCHAR2(50),
    MOBILIZATION_ID VARCHAR2(50),
    LICENSE_NUMBER VARCHAR2(100),
    CERTIFICATE_NUMBER VARCHAR2(100),
    PLATE_NUMBER VARCHAR2(50),
    LICENSE_EXPIRY_DATE DATE,
    CERTIFICATE_EXPIRY_DATE DATE,
    INSURANCE_EXPIRY_DATE DATE,
    REQUEST_DATE DATE,
    RECEIVED_DATE DATE,
    REQUEST_ID VARCHAR2(50),
    DEMOBILIZATION_DATE DATE,
    NOTES CLOB,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_EQUIPMENT_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID),
    CONSTRAINT FK_EQUIPMENT_SUBCONTRACTOR FOREIGN KEY (SUBCONTRACTOR_ID) REFERENCES SUBCONTRACTORS(ID)
);

6. Subcontractors

Subcontractor master data with banking information, insurance details, and performance metrics.

CREATE TABLE SUBCONTRACTORS (
    ID VARCHAR2(50) PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    COUNTRY VARCHAR2(50) NOT NULL CHECK (COUNTRY IN ('Libya', 'Tunisia', 'Iraq')),
    CONTACT_PERSON VARCHAR2(255) NOT NULL,
    EMAIL VARCHAR2(255) NOT NULL,
    PHONE VARCHAR2(50) NOT NULL,
    ADDRESS CLOB NOT NULL,
    REGISTRATION_NUMBER VARCHAR2(100) NOT NULL UNIQUE,
    TAX_NUMBER VARCHAR2(100) NOT NULL,
    STATUS VARCHAR2(20) DEFAULT 'active' CHECK (STATUS IN ('active', 'inactive')),
    EQUIPMENT_COUNT NUMBER DEFAULT 0,
    RATING NUMBER(3,2) DEFAULT 0,
    CONTRACT_START_DATE DATE,
    CONTRACT_END_DATE DATE,
    PAYMENT_TERMS VARCHAR2(100) DEFAULT 'Net 30',
    BANK_NAME VARCHAR2(255),
    BANK_ACCOUNT VARCHAR2(100),
    SWIFT_CODE VARCHAR2(20),
    INSURANCE_POLICY VARCHAR2(100),
    INSURANCE_EXPIRY DATE,
    CERTIFICATIONS CLOB, -- JSON stored as CLOB
    TOTAL_PAID NUMBER(15,2) DEFAULT 0,
    TOTAL_OUTSTANDING NUMBER(15,2) DEFAULT 0,
    AVERAGE_SCORE NUMBER(3,2),
    LAST_EVALUATION_DATE DATE,
    EVALUATION_COUNT NUMBER DEFAULT 0,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

7. Work Status Rates

Equipment work status rates for different operational states (Working, Standby, etc.).

CREATE TABLE WORK_STATUS_RATES (
    ID VARCHAR2(36) PRIMARY KEY,
    EQUIPMENT_ID VARCHAR2(50) NOT NULL,
    STATUS VARCHAR2(5) NOT NULL CHECK (STATUS IN ('W', 'S', 'NW', 'TG', 'TB')),
    RATE NUMBER(3,0) NOT NULL CHECK (RATE >= 0 AND RATE <= 100),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_WSR_EQUIPMENT FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(ID) ON DELETE CASCADE,
    CONSTRAINT UK_EQUIPMENT_STATUS UNIQUE (EQUIPMENT_ID, STATUS)
);

8. Timesheets

Daily equipment work tracking with operator information and work status.

CREATE TABLE TIMESHEETS (
    ID VARCHAR2(50) PRIMARY KEY,
    SHEET_DATE DATE NOT NULL,
    EQUIPMENT_ID VARCHAR2(50) NOT NULL,
    PROJECT_ID VARCHAR2(50) NOT NULL,
    WORK_STATUS VARCHAR2(5) NOT NULL CHECK (WORK_STATUS IN ('W', 'S', 'NW', 'TG', 'TB')),
    OPERATOR_NAME VARCHAR2(255) NOT NULL,
    NOTES CLOB,
    SUBMITTED_BY VARCHAR2(255) NOT NULL,
    SUBMITTED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    MOBILIZATION_DATE DATE,
    DEMOBILIZATION_DATE DATE,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_TIMESHEET_EQUIPMENT FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(ID),
    CONSTRAINT FK_TIMESHEET_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID)
);

Workflow & Approval Management

9. Equipment Requests

Equipment requisition requests with multi-level approval workflow support.

CREATE TABLE EQUIPMENT_REQUESTS (
    ID VARCHAR2(50) PRIMARY KEY,
    PROJECT_ID VARCHAR2(50) NOT NULL,
    EQUIPMENT_TYPE VARCHAR2(100) NOT NULL,
    MODEL VARCHAR2(100) NOT NULL,
    CONSTRUCTION_MANAGER_NAME VARCHAR2(255) NOT NULL,
    START_DATE DATE NOT NULL,
    FINISH_DATE DATE NOT NULL,
    NOTES CLOB,
    PROCESSED_BY VARCHAR2(255) NOT NULL,
    SUBMITTED_DATE DATE DEFAULT SYSDATE,
    STATUS VARCHAR2(30) DEFAULT 'pending_pl' CHECK (STATUS IN ('pending_pl', 'pending_pmo', 'pending_operations', 'pending_bod', 'approved', 'rejected')),
    MOBILIZATION_ID VARCHAR2(50),
    EQUIPMENT_ID VARCHAR2(50),
    DEMOBILIZATION_DATE DATE,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_REQ_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID),
    CONSTRAINT FK_REQ_EQUIPMENT FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(ID)
);

10. Request Approvals

Tracks approval workflow for equipment requests with approver information and comments.

CREATE TABLE REQUEST_APPROVALS (
    ID VARCHAR2(36) PRIMARY KEY,
    REQUEST_ID VARCHAR2(50) NOT NULL,
    APPROVER_NAME VARCHAR2(255),
    APPROVER_ROLE VARCHAR2(50) NOT NULL CHECK (APPROVER_ROLE IN ('PL', 'PMO', 'Operations Manager', 'BOD')),
    STATUS VARCHAR2(20) DEFAULT 'pending' CHECK (STATUS IN ('pending', 'approved', 'rejected')),
    APPROVAL_DATE TIMESTAMP,
    COMMENTS CLOB,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_APPROVAL_REQUEST FOREIGN KEY (REQUEST_ID) REFERENCES EQUIPMENT_REQUESTS(ID) ON DELETE CASCADE
);

11. Extension Requests

Equipment rental extension requests with justification and approval workflow.

CREATE TABLE EQUIPMENT_EXTENSION_REQUESTS (
    ID VARCHAR2(50) PRIMARY KEY,
    EQUIPMENT_ID VARCHAR2(50) NOT NULL,
    REQUEST_DATE DATE NOT NULL,
    CURRENT_END_DATE DATE NOT NULL,
    NEW_END_DATE DATE NOT NULL,
    REASON CLOB NOT NULL,
    STATUS VARCHAR2(30) DEFAULT 'pending_pl' CHECK (STATUS IN ('pending_pl', 'pending_pmo', 'pending_operations', 'pending_bod', 'approved', 'rejected')),
    INITIATED_BY VARCHAR2(255) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_EXT_EQUIPMENT FOREIGN KEY (EQUIPMENT_ID) REFERENCES EQUIPMENT(ID)
);

12. Extension Request Approvals

Approval workflow tracking for equipment extension requests.

CREATE TABLE EXTENSION_REQUEST_APPROVALS (
    ID VARCHAR2(36) PRIMARY KEY,
    EXTENSION_REQUEST_ID VARCHAR2(50) NOT NULL,
    APPROVER_NAME VARCHAR2(255),
    APPROVER_ROLE VARCHAR2(50) NOT NULL CHECK (APPROVER_ROLE IN ('PL', 'PMO', 'Operations Manager', 'BOD')),
    STATUS VARCHAR2(20) DEFAULT 'pending' CHECK (STATUS IN ('pending', 'approved', 'rejected')),
    APPROVAL_DATE TIMESTAMP,
    COMMENTS CLOB,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_EXT_APPROVAL_REQUEST FOREIGN KEY (EXTENSION_REQUEST_ID) REFERENCES EQUIPMENT_EXTENSION_REQUESTS(ID) ON DELETE CASCADE
);

Financial Management

13. Evaluation Criteria

Configurable evaluation criteria for subcontractor performance assessments.

CREATE TABLE EVALUATION_CRITERIA (
    ID VARCHAR2(50) PRIMARY KEY,
    NAME VARCHAR2(255) NOT NULL,
    DESCRIPTION CLOB NOT NULL,
    WEIGHT NUMBER(3,0) NOT NULL CHECK (WEIGHT >= 0 AND WEIGHT <= 100),
    MAX_SCORE NUMBER(2,0) DEFAULT 10,
    IS_ACTIVE NUMBER(1) DEFAULT 1,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

14. Subcontractor Evaluations

Comprehensive subcontractor performance evaluations with scoring and recommendations.

CREATE TABLE SUBCONTRACTOR_EVALUATIONS (
    ID VARCHAR2(50) PRIMARY KEY,
    SUBCONTRACTOR_ID VARCHAR2(50) NOT NULL,
    PROJECT_ID VARCHAR2(50) NOT NULL,
    EVALUATOR_NAME VARCHAR2(255) NOT NULL,
    EVALUATOR_ROLE VARCHAR2(100) NOT NULL,
    EVALUATION_DATE DATE NOT NULL,
    EVALUATION_PERIOD_START DATE NOT NULL,
    EVALUATION_PERIOD_END DATE NOT NULL,
    OVERALL_SCORE NUMBER(4,2) NOT NULL,
    OVERALL_RATING VARCHAR2(30) NOT NULL CHECK (OVERALL_RATING IN ('excellent', 'good', 'satisfactory', 'needs_improvement', 'poor')),
    STRENGTHS CLOB, -- JSON stored as CLOB
    AREAS_FOR_IMPROVEMENT CLOB, -- JSON stored as CLOB
    RECOMMENDATIONS CLOB,
    FUTURE_ENGAGEMENT VARCHAR2(30) NOT NULL CHECK (FUTURE_ENGAGEMENT IN ('highly_recommended', 'recommended', 'conditional', 'not_recommended')),
    STATUS VARCHAR2(20) DEFAULT 'draft' CHECK (STATUS IN ('draft', 'submitted', 'approved')),
    CREATED_BY VARCHAR2(255) NOT NULL,
    CREATED_DATE DATE DEFAULT SYSDATE,
    APPROVED_BY VARCHAR2(255),
    APPROVED_DATE DATE,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_EVAL_SUBCONTRACTOR FOREIGN KEY (SUBCONTRACTOR_ID) REFERENCES SUBCONTRACTORS(ID),
    CONSTRAINT FK_EVAL_PROJECT FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID)
);

15. Evaluation Scores

Detailed scoring for each evaluation criterion with comments.

CREATE TABLE EVALUATION_SCORES (
    ID VARCHAR2(36) PRIMARY KEY,
    EVALUATION_ID VARCHAR2(50) NOT NULL,
    CRITERIA_ID VARCHAR2(50) NOT NULL,
    SCORE NUMBER(2,0) NOT NULL CHECK (SCORE >= 0),
    COMMENTS CLOB,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_SCORE_EVALUATION FOREIGN KEY (EVALUATION_ID) REFERENCES SUBCONTRACTOR_EVALUATIONS(ID) ON DELETE CASCADE,
    CONSTRAINT FK_SCORE_CRITERIA FOREIGN KEY (CRITERIA_ID) REFERENCES EVALUATION_CRITERIA(ID)
);

Inspection Management

16. Inspection Checklists

Master inspection checklists for different equipment types with versioning support.

CREATE TABLE INSPECTION_CHECKLISTS (
    ID VARCHAR2(50) PRIMARY KEY,
    EQUIPMENT_TYPE VARCHAR2(100) NOT NULL,
    NAME VARCHAR2(255) NOT NULL,
    DESCRIPTION CLOB,
    VERSION VARCHAR2(20) DEFAULT '1.0',
    CREATED_BY VARCHAR2(255) NOT NULL,
    CREATED_DATE DATE DEFAULT SYSDATE,
    LAST_MODIFIED DATE DEFAULT SYSDATE,
    IS_ACTIVE NUMBER(1) DEFAULT 1,
    DEFAULT_FREQUENCY VARCHAR2(20) CHECK (DEFAULT_FREQUENCY IN ('daily', 'weekly', 'monthly', 'quarterly', 'semi-annually', 'annually', 'as-needed')),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

18. Inspection Checklist Items

Individual checklist items with different input types and validation options.

CREATE TABLE INSPECTION_CHECKLIST_ITEMS (
    ID VARCHAR2(50) PRIMARY KEY,
    CHECKLIST_ID VARCHAR2(50) NOT NULL,
    NAME VARCHAR2(255) NOT NULL,
    DESCRIPTION CLOB,
    CATEGORY VARCHAR2(100) NOT NULL,
    IS_REQUIRED NUMBER(1) DEFAULT 1,
    INPUT_TYPE VARCHAR2(20) NOT NULL CHECK (INPUT_TYPE IN ('checkbox', 'text', 'select', 'number', 'date')),
    OPTIONS CLOB, -- JSON stored as CLOB
    ORDER_INDEX NUMBER NOT NULL,
    FREQUENCY VARCHAR2(20) CHECK (FREQUENCY IN ('daily', 'weekly', 'monthly', 'quarterly', 'semi-annually', 'annually', 'as-needed')),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_ITEM_CHECKLIST FOREIGN KEY (CHECKLIST_ID) REFERENCES INSPECTION_CHECKLISTS(ID) ON DELETE CASCADE
);

19. Inspection Templates

Inspection templates with safety requirements and tool specifications.

CREATE TABLE INSPECTION_TEMPLATES (
    ID VARCHAR2(50) PRIMARY KEY,
    EQUIPMENT_TYPE VARCHAR2(100) NOT NULL,
    CHECKLIST_ID VARCHAR2(50) NOT NULL,
    NAME VARCHAR2(255) NOT NULL,
    DESCRIPTION CLOB,
    ESTIMATED_DURATION NUMBER, -- in minutes
    REQUIRED_CERTIFICATIONS CLOB, -- JSON stored as CLOB
    SAFETY_REQUIREMENTS CLOB, -- JSON stored as CLOB
    TOOLS CLOB, -- JSON stored as CLOB
    CREATED_BY VARCHAR2(255) NOT NULL,
    CREATED_DATE DATE DEFAULT SYSDATE,
    IS_ACTIVE NUMBER(1) DEFAULT 1,
    FREQUENCY VARCHAR2(20) NOT NULL CHECK (FREQUENCY IN ('daily', 'weekly', 'monthly', 'quarterly', 'semi-annually', 'annually', 'as-needed')),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_TEMPLATE_CHECKLIST FOREIGN KEY (CHECKLIST_ID) REFERENCES INSPECTION_CHECKLISTS(ID)
);

Performance Optimization

Oracle Indexes

-- Performance indexes for query optimization
CREATE INDEX IDX_EQUIPMENT_PROJECT_ID ON EQUIPMENT(PROJECT_ID);
CREATE INDEX IDX_EQUIPMENT_SUBCONTRACTOR_ID ON EQUIPMENT(SUBCONTRACTOR_ID);
CREATE INDEX IDX_EQUIPMENT_STATUS ON EQUIPMENT(STATUS);
CREATE INDEX IDX_TIMESHEETS_DATE ON TIMESHEETS(SHEET_DATE);
CREATE INDEX IDX_TIMESHEETS_EQUIPMENT_ID ON TIMESHEETS(EQUIPMENT_ID);
CREATE INDEX IDX_TIMESHEETS_PROJECT_ID ON TIMESHEETS(PROJECT_ID);
CREATE INDEX IDX_EQUIPMENT_REQUESTS_STATUS ON EQUIPMENT_REQUESTS(STATUS);
CREATE INDEX IDX_EQUIPMENT_SERIAL_NUMBER ON EQUIPMENT(SERIAL_NUMBER);
CREATE INDEX IDX_SUBCONTRACTORS_REGISTRATION ON SUBCONTRACTORS(REGISTRATION_NUMBER);
CREATE INDEX IDX_USERS_USERNAME ON USERS(USERNAME);
CREATE INDEX IDX_USERS_EMAIL ON USERS(EMAIL);

-- Composite indexes for complex queries
CREATE INDEX IDX_EQUIPMENT_STATUS_PROJECT ON EQUIPMENT(STATUS, PROJECT_ID);
CREATE INDEX IDX_TIMESHEETS_DATE_PROJECT ON TIMESHEETS(SHEET_DATE, PROJECT_ID);
CREATE INDEX IDX_EQUIPMENT_TYPE_STATUS ON EQUIPMENT(TYPE, STATUS);

Oracle Sequences

-- Sequences for auto-generating IDs
CREATE SEQUENCE SEQ_USER_PROFILES START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_USERS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_WORK_STATUS_RATES START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_REQUEST_APPROVALS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_EXTENSION_REQUEST_APPROVALS START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_EVALUATION_SCORES START WITH 1 INCREMENT BY 1;

Automatic Timestamp Triggers

-- Trigger for PROJECTS table
CREATE OR REPLACE TRIGGER TRG_PROJECTS_UPDATED_AT
    BEFORE UPDATE ON PROJECTS
    FOR EACH ROW
BEGIN
    :NEW.UPDATED_AT := CURRENT_TIMESTAMP;
END;

-- Trigger for EQUIPMENT table
CREATE OR REPLACE TRIGGER TRG_EQUIPMENT_UPDATED_AT
    BEFORE UPDATE ON EQUIPMENT
    FOR EACH ROW
BEGIN
    :NEW.UPDATED_AT := CURRENT_TIMESTAMP;
END;

-- Trigger for SUBCONTRACTORS table
CREATE OR REPLACE TRIGGER TRG_SUBCONTRACTORS_UPDATED_AT
    BEFORE UPDATE ON SUBCONTRACTORS
    FOR EACH ROW
BEGIN
    :NEW.UPDATED_AT := CURRENT_TIMESTAMP;
END;

Database Features

Data Integrity

Comprehensive CHECK constraints and foreign key relationships ensure data consistency and business rule enforcement.

Automatic Timestamps

Oracle triggers automatically maintain created_at and updated_at timestamps for audit trail and change tracking.

Multi-Country Support

Built-in support for Libya, Tunisia, and Iraq with country-specific validation and data segregation.

Performance Optimized

Strategic indexing and composite indexes for optimal query performance on large datasets.

Security

Role-based access control integration with Spring Security and comprehensive audit trails.

Scalable Design

Designed to handle enterprise-scale data volumes with efficient storage and query patterns.