โ† RequirementsOpen UI mockup โ†’
SQL Server / Azure SQL

Intelli Onboard Table Creation

Idempotent creation script for the tenant-aware Student and StudentDocument tables, including foreign keys, uniqueness rules, and lookup indexes.

Checks before table creation

Safe to run repeatedly: The script checks that the shared Tenant and UserExtended tables exist, checks each new table with OBJECT_ID, and checks every index through sys.indexes before creating it. Existing tables and data are not dropped or overwritten.

Complete query

/*
    Intelli Onboard - SQL Server / Azure SQL schema
    Safe to re-run: every table and index is checked before creation.
    Requires existing dbo.Tenant(Id) and dbo.UserExtended(Id).
*/

SET XACT_ABORT ON;
GO

IF OBJECT_ID(N'dbo.Tenant', N'U') IS NULL
    THROW 50001, 'Required table dbo.Tenant does not exist. Create the shared Tenant table first.', 1;
GO

IF OBJECT_ID(N'dbo.UserExtended', N'U') IS NULL
    THROW 50002, 'Required table dbo.UserExtended does not exist. Create the shared UserExtended table first.', 1;
GO

/* 1. Student: one-to-one with UserExtended */
IF OBJECT_ID(N'dbo.Student', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Student
    (
        UserExtendedId BIGINT NOT NULL,
        TenantId BIGINT NOT NULL,
        UIDNumber NVARCHAR(100) NULL,
        FatherName NVARCHAR(200) NULL,
        MotherName NVARCHAR(200) NULL,
        CreatedBy BIGINT NULL,
        CreatedOn DATETIME2(3) NOT NULL
            CONSTRAINT DF_Student_CreatedOn DEFAULT SYSUTCDATETIME(),
        UpdatedBy BIGINT NULL,
        UpdatedOn DATETIME2(3) NULL,
        IsActive BIT NOT NULL
            CONSTRAINT DF_Student_IsActive DEFAULT (1),

        CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (UserExtendedId),
        CONSTRAINT FK_Student_UserExtended
            FOREIGN KEY (UserExtendedId) REFERENCES dbo.UserExtended(Id),
        CONSTRAINT FK_Student_Tenant
            FOREIGN KEY (TenantId) REFERENCES dbo.Tenant(Id),
        CONSTRAINT FK_Student_CreatedBy
            FOREIGN KEY (CreatedBy) REFERENCES dbo.UserExtended(Id),
        CONSTRAINT FK_Student_UpdatedBy
            FOREIGN KEY (UpdatedBy) REFERENCES dbo.UserExtended(Id)
    );
END;
GO

IF OBJECT_ID(N'dbo.Student', N'U') IS NOT NULL
   AND NOT EXISTS
   (
       SELECT 1 FROM sys.indexes
       WHERE object_id = OBJECT_ID(N'dbo.Student')
         AND name = N'IX_Student_TenantId'
   )
BEGIN
    CREATE INDEX IX_Student_TenantId
        ON dbo.Student(TenantId, UserExtendedId);
END;
GO

IF OBJECT_ID(N'dbo.Student', N'U') IS NOT NULL
   AND NOT EXISTS
   (
       SELECT 1 FROM sys.indexes
       WHERE object_id = OBJECT_ID(N'dbo.Student')
         AND name = N'UX_Student_TenantId_UIDNumber'
   )
BEGIN
    CREATE UNIQUE INDEX UX_Student_TenantId_UIDNumber
        ON dbo.Student(TenantId, UIDNumber)
        WHERE UIDNumber IS NOT NULL;
END;
GO

/* 2. StudentDocument: metadata for files stored in a private Blob container */
IF OBJECT_ID(N'dbo.StudentDocument', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.StudentDocument
    (
        Id BIGINT IDENTITY(1,1) NOT NULL,
        TenantId BIGINT NOT NULL,
        UserExtendedId BIGINT NOT NULL,
        DocumentName NVARCHAR(500) NOT NULL,
        FileSize BIGINT NOT NULL,
        FileType NVARCHAR(150) NOT NULL,
        Extension NVARCHAR(30) NOT NULL,
        FilePath NVARCHAR(1000) NOT NULL,
        CreatedBy BIGINT NULL,
        CreatedOn DATETIME2(3) NOT NULL
            CONSTRAINT DF_StudentDocument_CreatedOn DEFAULT SYSUTCDATETIME(),
        UpdatedBy BIGINT NULL,
        UpdatedOn DATETIME2(3) NULL,
        IsActive BIT NOT NULL
            CONSTRAINT DF_StudentDocument_IsActive DEFAULT (1),

        CONSTRAINT PK_StudentDocument PRIMARY KEY CLUSTERED (Id),
        CONSTRAINT FK_StudentDocument_Student
            FOREIGN KEY (UserExtendedId) REFERENCES dbo.Student(UserExtendedId),
        CONSTRAINT FK_StudentDocument_Tenant
            FOREIGN KEY (TenantId) REFERENCES dbo.Tenant(Id),
        CONSTRAINT FK_StudentDocument_CreatedBy
            FOREIGN KEY (CreatedBy) REFERENCES dbo.UserExtended(Id),
        CONSTRAINT FK_StudentDocument_UpdatedBy
            FOREIGN KEY (UpdatedBy) REFERENCES dbo.UserExtended(Id),
        CONSTRAINT CK_StudentDocument_FileSize
            CHECK (FileSize >= 0)
    );
END;
GO

IF OBJECT_ID(N'dbo.StudentDocument', N'U') IS NOT NULL
   AND NOT EXISTS
   (
       SELECT 1 FROM sys.indexes
       WHERE object_id = OBJECT_ID(N'dbo.StudentDocument')
         AND name = N'IX_StudentDocument_Tenant_Student'
   )
BEGIN
    CREATE INDEX IX_StudentDocument_Tenant_Student
        ON dbo.StudentDocument(TenantId, UserExtendedId, IsActive);
END;
GO

IF OBJECT_ID(N'dbo.StudentDocument', N'U') IS NOT NULL
   AND NOT EXISTS
   (
       SELECT 1 FROM sys.indexes
       WHERE object_id = OBJECT_ID(N'dbo.StudentDocument')
         AND name = N'UX_StudentDocument_FilePath'
   )
BEGIN
    CREATE UNIQUE INDEX UX_StudentDocument_FilePath
        ON dbo.StudentDocument(FilePath);
END;
GO

PRINT 'Intelli Onboard tables and indexes are ready.';
GO