DATABASE NAME : AOL_MASTER_CHECKLIST


1. Checklist Master

CREATE TABLE ChecklistMaster
(
    ChecklistId INT IDENTITY(1,1) PRIMARY KEY,

    ChecklistNumber NVARCHAR(100) NOT NULL,
    ChecklistName NVARCHAR(500) NOT NULL,

    ChecklistType NVARCHAR(100),
    Frequency NVARCHAR(100),

    SOPNumber NVARCHAR(100),
    Department NVARCHAR(200),

    VersionNo NVARCHAR(50),
    EffectiveDate DATE,

    Status NVARCHAR(50),

    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT(GETDATE()),

    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME,

    -- Extra Fields
    ExtraText1 NVARCHAR(500),
    ExtraText2 NVARCHAR(500),
    ExtraText3 NVARCHAR(500),
    ExtraText4 NVARCHAR(500),

    ExtraDate1 DATETIME,
    ExtraDate2 DATETIME,
    ExtraDate3 DATETIME,
    ExtraDate4 DATETIME
);


...........................................................................................

2. Checklist Item Master

CREATE TABLE ChecklistItemMaster
(
    ItemId INT IDENTITY(1,1) PRIMARY KEY,

    ChecklistId INT NOT NULL,

    SequenceNo INT,

    CheckPoints NVARCHAR(MAX),

    ResponseType NVARCHAR(50),

    IsMandatory BIT DEFAULT(1),

    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT(GETDATE()),

    -- Extra Fields
    ExtraText1 NVARCHAR(500),
    ExtraText2 NVARCHAR(500),
    ExtraText3 NVARCHAR(500),
    ExtraText4 NVARCHAR(500),

    ExtraDate1 DATETIME,
    ExtraDate2 DATETIME,
    ExtraDate3 DATETIME,
    ExtraDate4 DATETIME,

    CONSTRAINT FK_ChecklistItemMaster
        FOREIGN KEY(ChecklistId)
        REFERENCES ChecklistMaster(ChecklistId)
);


..........................................................................................


3. Checklist Master Equipment

CREATE TABLE ChecklistMasterEquipment
(
    ChecklistEquipmentId INT IDENTITY(1,1) PRIMARY KEY,

    ChecklistId INT NOT NULL,

    EquipmentCode NVARCHAR(100),

    EquipmentName NVARCHAR(500),

    EquipmentType NVARCHAR(100),

    Department NVARCHAR(200),

    Location NVARCHAR(300),

    Frequency NVARCHAR(100),

    PMDate DATE,

    NextPMDate DATE,

    
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT(GETDATE()),

    -- Extra Fields
    ExtraText1 NVARCHAR(500),
    ExtraText2 NVARCHAR(500),
    ExtraText3 NVARCHAR(500),
    ExtraText4 NVARCHAR(500),

    ExtraDate1 DATETIME,
    ExtraDate2 DATETIME,
    ExtraDate3 DATETIME,
    ExtraDate4 DATETIME,

    CONSTRAINT FK_ChecklistEquipment
        FOREIGN KEY(ChecklistId)
        REFERENCES ChecklistMaster(ChecklistId)
);

.......................................................................

4. Checklist Execution

CREATE TABLE ChecklistExecution
(
    ExecutionId INT IDENTITY(1,1) PRIMARY KEY,

    ChecklistId INT NOT NULL,

    ChecklistEquipmentId INT NOT NULL,

    ExecutionNumber NVARCHAR(100),

    PerformedBy NVARCHAR(100),

    PerformedDate DATETIME,

    Remarks NVARCHAR(MAX),

    ApprovedBy NVARCHAR(100),

    ApprovedDate DATETIME,

    ApprovalStatus NVARCHAR(50),

    CreatedDate DATETIME DEFAULT(GETDATE()),

    -- Extra Fields
    ExtraText1 NVARCHAR(500),
    ExtraText2 NVARCHAR(500),
    ExtraText3 NVARCHAR(500),
    ExtraText4 NVARCHAR(500),

    ExtraDate1 DATETIME,
    ExtraDate2 DATETIME,
    ExtraDate3 DATETIME,
    ExtraDate4 DATETIME,

    CONSTRAINT FK_Execution_Checklist
        FOREIGN KEY(ChecklistId)
        REFERENCES ChecklistMaster(ChecklistId),

    CONSTRAINT FK_Execution_Equipment
        FOREIGN KEY(ChecklistEquipmentId)
        REFERENCES ChecklistMasterEquipment(ChecklistEquipmentId)
);


............................................................................

5. Checklist Execution Details

CREATE TABLE ChecklistExecutionDetails
(
    DetailId INT IDENTITY(1,1) PRIMARY KEY,

    ExecutionId INT NOT NULL,

    ItemId INT NOT NULL,

    Observation NVARCHAR(MAX),

    Remarks NVARCHAR(MAX),

    Completed NVARCHAR(100),


    CreatedDate DATETIME DEFAULT(GETDATE()),

    -- Extra Fields
    ExtraText1 NVARCHAR(500),
    ExtraText2 NVARCHAR(500),
    ExtraText3 NVARCHAR(500),
    ExtraText4 NVARCHAR(500),

    ExtraDate1 DATETIME,
    ExtraDate2 DATETIME,
    ExtraDate3 DATETIME,
    ExtraDate4 DATETIME,

    CONSTRAINT FK_Details_Execution
        FOREIGN KEY(ExecutionId)
        REFERENCES ChecklistExecution(ExecutionId),

    CONSTRAINT FK_Details_Item
        FOREIGN KEY(ItemId)
        REFERENCES ChecklistItemMaster(ItemId)
);