sagar
/
practicekea_backend
1
1
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
practicekea_backend/database/create_table_scripts/CreateTables.sql

1236 lines
41 KiB
MySQL
Raw Permalink Normal View History

.net9.0
2024-12-02 13:24:34 +00:00
USE odiproj1_oa
--use OnlineAssessment
GO
--================================================
CREATE TABLE dbo.Roles (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[code] VARCHAR (10),
[name] VARCHAR (500) NOT NULL,
[access_level] SMALLINT NOT NULL,
[description] VARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1)
CONSTRAINT UNIQUE_Roles_code_name UNIQUE (code,[name]),
CONSTRAINT UNIQUE_Roles_accessLevel UNIQUE (access_level)
);
GO
--================================================
CREATE TABLE dbo.Plans (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[name] VARCHAR (500) NOT NULL,
[description] VARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT UNIQUE_Plans_name UNIQUE ([name])
);
GO
--================================================
CREATE TABLE dbo.Subscriptions (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[plan_id] INT NOT NULL,
[name] VARCHAR (500) NOT NULL,
[description] VARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT UNIQUE_Subscriptions_name UNIQUE (institute_id,plan_id,[name]),
CONSTRAINT FK_SubscriptionPlan FOREIGN KEY (plan_id) REFERENCES Plans (id)
);
GO
--================================================
CREATE TABLE dbo.Languages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[code] VARCHAR (10) NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT UNIQUE_Language_code UNIQUE (code),
CONSTRAINT UNIQUE_Language_code_name UNIQUE (code,[name])
);
GO
--================================================
CREATE TABLE dbo.States (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[code] VARCHAR (50) NOT NULL,
[name] VARCHAR (500) NOT NULL,
[language_id] INT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_States_Langugage FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_States_code_name UNIQUE (code,[name])
);
GO
--================================================
CREATE TABLE dbo.Institutes (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[name] VARCHAR (500) NOT NULL,
[domain] VARCHAR (500) NULL,
[api_key] VARCHAR (500) NULL,
[date_of_establishment] DATETIME,
[address] VARCHAR (1500) NULL,
[city] VARCHAR (1500) NULL,
[state_id] INT NULL,
[country] VARCHAR (1500) NULL,
[pin_code] VARCHAR (6) NULL,
[logo] VARCHAR (1000) NULL,
[image_url_small] VARCHAR (1000) NULL,
[image_url_large] VARCHAR (1000) NULL,
[subscription_id] INT,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1)
CONSTRAINT FK_Institute_State FOREIGN KEY (state_id) REFERENCES States(id),
CONSTRAINT FK_Institute_Subscription FOREIGN KEY (subscription_id) REFERENCES Subscriptions(id),
CONSTRAINT UNIQUE_Institute_name UNIQUE ([name])
);
GO
--================================================
CREATE TABLE dbo.Modules (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[name] VARCHAR(1000) NOT NULL,
[description] VARCHAR(MAX),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT UNIQUE_Modules_name UNIQUE ([name])
);
GO
--================================================
CREATE TABLE dbo.ModuleRoles (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[module_id] INT NOT NULL,
[role_id] INT NOT NULL,
[name] VARCHAR(1000),
[is_add] BIT DEFAULT(1),
[is_view] BIT DEFAULT(1),
[is_edit] BIT DEFAULT(1),
[is_delete] BIT DEFAULT(0),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1)
CONSTRAINT FK_ModuleRoles_institute FOREIGN KEY (institute_id) REFERENCES Institutes (id),
CONSTRAINT FK_ModuleRoles_module FOREIGN KEY (module_id) REFERENCES Modules (id),
CONSTRAINT FK_ModuleRoles_Roles FOREIGN KEY (role_id) REFERENCES Roles (id)
);
GO
--================================================
CREATE TABLE dbo.Users (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[role_id] INT NOT NULL,
[institute_id] INT NOT NULL,
[language_id] INT NULL,
[registration_id] INT,
[registration_datetime] DATETIME,
[first_name] VARCHAR (50) NOT NULL,
[last_name] VARCHAR (50) NULL,
[date_of_birth] DATE,
[gender] VARCHAR (10),
[email_id] VARCHAR (500),
[mobile_no] VARCHAR (10),
[photo] VARCHAR (1000),
[address] VARCHAR (1500),
[city] VARCHAR (1500),
[state_id] INT,
[country] VARCHAR (1500),
[pin_code] VARCHAR (6) NULL,
[latitude] VARCHAR (20),
[longitude] VARCHAR (20),
[user_password] NVARCHAR(500),
[user_salt] VARCHAR(10),
[access_token] NVARCHAR(100),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT(1)
CONSTRAINT UNIQUE_Users_Institute_Email UNIQUE (institute_id, email_id),
CONSTRAINT FK_User_Role FOREIGN KEY (role_id) REFERENCES Roles (id),
CONSTRAINT FK_User_Institute FOREIGN KEY (institute_id) REFERENCES Institutes (id),
CONSTRAINT FK_User_Language FOREIGN KEY (language_id) REFERENCES Languages(id),
CONSTRAINT FK_User_State FOREIGN KEY (state_id) REFERENCES States(id)
);
GO
--================================================
CREATE TABLE dbo.Classes (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT(1)
CONSTRAINT FK_Classes_Institute FOREIGN KEY (institute_id) REFERENCES institutes (id)
--CONSTRAINT UNIQUE_Classes_name UNIQUE (institute_id,[name])
);
CREATE UNIQUE INDEX UNIQUE_Classes_name ON dbo.Classes (institute_id, name, is_active) WHERE is_active=1
GO
/*--================================================
CREATE TABLE dbo.ClassLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT NOT NULL DEFAULT(1),
[class_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[description] NVARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT(1),
CONSTRAINT FK_ClassLanguages_Class FOREIGN KEY (class_id) REFERENCES Classes (id),
CONSTRAINT FK_ClassLanguages_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_ClassLanguages_name UNIQUE (language_id,class_id,[name])
);
GO
--================================================
CREATE TABLE dbo.ClassTeachers (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[class_id] INT NOT NULL,
[user_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT(1)
CONSTRAINT FK_ClassTeachers_Class FOREIGN KEY (class_id) REFERENCES Classes (id),
CONSTRAINT FK_ClassTeachers_User FOREIGN KEY (user_id) REFERENCES Users (id),
CONSTRAINT UNIQUE_ClassTeacher UNIQUE (class_id,[user_id])
)
*/
--================================================
CREATE TABLE dbo.UserGroups (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[class_id] INT NOT NULL,
[name] VARCHAR(1000),
[description] VARCHAR(MAX),
[photo] IMAGE,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_UserGroups_Class FOREIGN KEY (class_id) REFERENCES Classes (id),
--CONSTRAINT UNIQUE_UserGroups UNIQUE (class_id,[name])
);
GO
CREATE UNIQUE INDEX UNIQUE_UserGroups ON dbo.UserGroups (class_id, name, is_active) WHERE is_active=1
--================================================
CREATE TABLE dbo.UserGroupMembers (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_group_id] INT NOT NULL,
[user_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT(1),
CONSTRAINT FK_UserGroupMembers_UserGroup FOREIGN KEY (user_group_id) REFERENCES UserGroups (id),
CONSTRAINT FK_UserGroupMembers_User FOREIGN KEY (user_id) REFERENCES Users (id),
--CONSTRAINT UNIQUE_UserGroupsMember UNIQUE (user_group_id,[user_id])
);
GO
CREATE UNIQUE INDEX UNIQUE_UserGroupsMember ON dbo.UserGroupMembers (user_group_id, user_id, is_active) WHERE is_active=1
--================================================
CREATE TABLE dbo.Subjects (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[class_id] INT NOT NULL,
[name] NVARCHAR (1500) NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT(1)
CONSTRAINT FK_SubjectClass FOREIGN KEY (class_id) REFERENCES Classes (id)
--CONSTRAINT UNIQUE_Subjects_name UNIQUE (class_id,[name])
);
CREATE UNIQUE INDEX UNIQUE_Subjects_name ON dbo.Subjects (class_id, name, is_active) WHERE is_active=1
GO
/*
--================================================
CREATE TABLE dbo.SubjectLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT NOT NULL DEFAULT(1),
[subject_id] INT NOT NULL,
[name] NVARCHAR (1500) NOT NULL,
[description] NVARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1)
CONSTRAINT FK_SubjectLanguages_Subject FOREIGN KEY (subject_id) REFERENCES Subjects (id),
CONSTRAINT FK_SubjectLanguages_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_SubjectName UNIQUE (language_id,subject_id,[name])
);
*/
--================================================
-- Which user has access to which subject
--================================================
-- CREATE TABLE dbo.UserSubjects (
-- [id] INT PRIMARY KEY IDENTITY (1, 1),
-- [user_id] INT NOT NULL,
-- [subject_id] INT NOT NULL,
-- [is_active] BIT DEFAULT(1)
-- CONSTRAINT FK_UserSubjects_Subject FOREIGN KEY (subject_id) REFERENCES Subjects (id),
-- CONSTRAINT FK_UserSubjects_User FOREIGN KEY (user_id) REFERENCES Users (id)
-- )
--================================================
CREATE TABLE dbo.Categories (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[subject_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT(1)
CONSTRAINT FK_CategoriesSubject FOREIGN KEY (subject_id) REFERENCES Subjects (id)
--CONSTRAINT UNIQUE_Categories_name UNIQUE (subject_id,[name])
);
CREATE UNIQUE INDEX UNIQUE_Categories_name ON dbo.Categories (subject_id, name, is_active) WHERE is_active=1
GO
/*
--================================================
CREATE TABLE dbo.CategoryLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT NOT NULL DEFAULT(1),
[category_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[description] NVARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_CategoryLanguages_Subject FOREIGN KEY (category_id) REFERENCES Categories (id),
CONSTRAINT FK_CategoryLanguages_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_CategoryName UNIQUE (language_id,category_id,[name])
);
--================================================
CREATE TABLE dbo.SubCategories (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[category_id] INT NOT NULL,
[photo] IMAGE,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT(1),
CONSTRAINT FK_SubCategories_Category FOREIGN KEY (category_id) REFERENCES Categories (id)
);
GO
--================================================
CREATE TABLE dbo.SubCategoryLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT NOT NULL DEFAULT(1),
[category_id] INT NOT NULL,
[subcategory_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[description] NVARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_SubCategoryLanguages_SubCategory FOREIGN KEY (subcategory_id) REFERENCES SubCategories (id),
CONSTRAINT FK_SubCategoryLanguages_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_SubCategoryName UNIQUE (language_id,category_id,subcategory_id,[name])
);
*/
--================================================
CREATE TABLE dbo.Tags (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL UNIQUE,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT(1)
);
CREATE UNIQUE INDEX UNIQUE_Tags_name ON dbo.Tags (institute_id, name, is_active) WHERE is_active=1
GO
/*
--================================================
CREATE TABLE dbo.TagLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT NOT NULL DEFAULT(1),
[tag_id] INT NOT NULL,
[name] NVARCHAR (500) NOT NULL,
[description] NVARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_TagLanguages_Tag FOREIGN KEY (tag_id) REFERENCES Tags (id),
CONSTRAINT FK_TagLanguages_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_TagName UNIQUE (language_id,tag_id,[name])
);
GO
*/
--================================================
CREATE TABLE dbo.QuestionTypes (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[code] NVARCHAR (10) NOT NULL UNIQUE,
[name] NVARCHAR (500) NOT NULL UNIQUE,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
/* CONSTRAINT FK_QuestionTypes_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_QuestionTypesName UNIQUE (code, name)*/
);
GO
--================================================
CREATE TABLE dbo.Questions (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[author_id] INT NOT NULL,
[status_code] VARCHAR(10),
[complexity_code] SMALLINT,
[image] VARCHAR (1000) NULL,
[source] VARCHAR (1500) NULL,
[type_id] INT NOT NULL DEFAULT(1),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_Questions_Institutes FOREIGN KEY (institute_id) REFERENCES Institutes (id),
CONSTRAINT FK_Questions_Type FOREIGN KEY (type_id) REFERENCES QuestionTypes (id),
CONSTRAINT FK_Questions_Author FOREIGN KEY (author_id) REFERENCES Users (id)
);
GO
--================================================
CREATE TABLE dbo.QuestionLanguges(
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT,
[question_id] INT,
[question] NVARCHAR (2500) NOT NULL,
[description] NVARCHAR (MAX) NULL,
[direction] NVARCHAR (1500) NULL,
[answer_explanation] NVARCHAR(MAX),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_QuestionLanguges_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT FK_QuestionLanguges_Questions FOREIGN KEY (question_id) REFERENCES Questions (id),
--CONSTRAINT UNIQUE_Question UNIQUE (is_active,language_id,question_id,[question])
)
CREATE UNIQUE INDEX UNIQUE_Question ON dbo.QuestionLanguges (question_id, language_id, is_active) WHERE is_active=1
--================================================
CREATE TABLE dbo.QuestionOptions (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[question_id] INT NOT NULL,
[option_image] IMAGE NULL,
[is_correct] BIT DEFAULT (0),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_Options_Question FOREIGN KEY (question_id) REFERENCES Questions (id)
);
GO
--================================================
CREATE TABLE dbo.QuestionOptionLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT,
[question_id] INT NOT NULL,
[question_option_id] INT NOT NULL,
[option_text] NVARCHAR (500) NOT NULL,
[description] NVARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_QuestionOptionLanguages_Languages FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT FK_QuestionOptionLanguages_QuestionOption FOREIGN KEY (question_option_id) REFERENCES QuestionOptions (id),
--CONSTRAINT UNIQUE_QuestionOption UNIQUE (language_id,question_id, question_option_id,[option_text])
);
GO
CREATE UNIQUE INDEX UNIQUE_QuestionOption ON dbo.QuestionOptionLanguages (question_option_id, language_id, is_active) WHERE is_active=1
--======================================
CREATE TABLE dbo.QuestionTags (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[question_id] INT NOT NULL,
[tag_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_QuestionTags_Question FOREIGN KEY (question_id) REFERENCES Questions (id),
CONSTRAINT FK_QuestionTags_Tag FOREIGN KEY (tag_id) REFERENCES Tags (id)
)
CREATE UNIQUE INDEX UNIQUE_QuestionTags ON dbo.QuestionTags (question_id, tag_id, is_active) WHERE is_active=1
--======================================
CREATE TABLE dbo.QuestionCategories (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[question_id] INT NOT NULL,
[category_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_QuestionCategories_Question FOREIGN KEY (question_id) REFERENCES Questions (id),
CONSTRAINT FK_QuestionCategories_Category FOREIGN KEY (category_id) REFERENCES Categories (id)
)
CREATE UNIQUE INDEX UNIQUE_QuestionCategories ON dbo.QuestionCategories (question_id, category_id, is_active) WHERE is_active=1
--================================================
-- EXAM
--================================================
CREATE TABLE dbo.ExamTypes (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[code] VARCHAR (10) UNIQUE, --Complete / Subject based/Previous year
[name] VARCHAR (500) NOT NULL UNIQUE, --Complete / Subject based/Previous year
[description] VARCHAR (1500),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT UNIQUE_ExamTypes UNIQUE (is_active,code,[name])
);
GO
--================================================
-- This is a master table to store all the exams
--================================================
CREATE TABLE dbo.Exams (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[class_id] INT NOT NULL, -- added
[language_id] INT NOT NULL,
[exam_type_id] INT NOT NULL,
[name] VARCHAR (500) NOT NULL,
[instruction] NVARCHAR (1500),
[exam_status] VARCHAR(10) NOT NULL, --draft/published/inactive
[exam_open_datetime] DATETIME,
[exam_close_datetime] DATETIME,
[exam_duration_in_seconds] INT, -- updated name
[attempts_allowed] SMALLINT,
[is_random_question] VARCHAR(1) DEFAULT('Y'), -- updated name
[complexity] SMALLINT, -- short int
[photo] VARCHAR,
[total_marks] SMALLINT,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_Exams_Institutes FOREIGN KEY (institute_id) REFERENCES Institutes (id),
CONSTRAINT FK_Exams_Type FOREIGN KEY (exam_type_id) REFERENCES ExamTypes (id),
CONSTRAINT FK_Exams_CreatedByUser FOREIGN KEY (created_by) REFERENCES Users (id)
);
GO
--================================================
-- This stores all the exams of the classes
--================================================
CREATE TABLE dbo.UserGroupExams (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_group_id] INT NOT NULL,
[exam_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_UserGroupExams_UserGroup FOREIGN KEY (user_group_id) REFERENCES UserGroups (id),
CONSTRAINT FK_UserGroupExams_Exams FOREIGN KEY (exam_id) REFERENCES Exams (id)
);
GO
CREATE TABLE dbo.UserGroupPractices (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_group_id] INT NOT NULL,
[practice_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_UserGroupPractices_UserGroup FOREIGN KEY (user_group_id) REFERENCES UserGroups (id),
CONSTRAINT FK_UserGroupPractices_Practices FOREIGN KEY (practice_id) REFERENCES Practices (id)
);
GO
/*
--================================================
CREATE TABLE dbo.ExamLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT,
[exam_id] INT NOT NULL,
[name] VARCHAR (500) NOT NULL,
[description] NVARCHAR (1500),
[instruction] NVARCHAR (1500),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamLanguages_Language FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT FK_ExamLanguages_Exams FOREIGN KEY (exam_id) REFERENCES Exams (id),
CONSTRAINT UNIQUE_Exam UNIQUE (language_id,exam_id,[name])
);
*/
--================================================
-- This stores the sections of an exam
--================================================
CREATE TABLE dbo.ExamSections (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[exam_id] INT NOT NULL,
[subject_id] INT NOT NULL,
[user_id] INT NOT NULL, --Author
[subject_sequence] SMALLINT,
[subject_duration] SMALLINT,
[total_marks] SMALLINT,
[status] VARCHAR(15),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamSections_Exam FOREIGN KEY (exam_id) REFERENCES Exams (id),
CONSTRAINT FK_ExamSections_Subject FOREIGN KEY (subject_id) REFERENCES Subjects (id),
CONSTRAINT FK_ExamSections_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
CREATE UNIQUE INDEX UNIQUE_ExamSections ON dbo.ExamSections (exam_id, subject_id, is_active) WHERE is_active=1
--================================================
CREATE TABLE dbo.ExamQuestionsMarkWeight (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[exam_section_id] INT NOT NULL,
[question_id] INT NOT NULL,
[mark_for_correct_answer] FLOAT,
[mark_for_wrong_answer] FLOAT,
[total_marks] SMALLINT,
[question_sequence] SMALLINT,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamQuestionsMarkWeight_ExamSection FOREIGN KEY (exam_section_id) REFERENCES ExamSections (id),
CONSTRAINT FK_ExamQuestionsMarkWeight_Question FOREIGN KEY (question_id) REFERENCES Questions (id)
);
GO
--================================================
CREATE TABLE dbo.ExamAttempts (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[exam_id] INT NOT NULL,
[remaining_time_seconds] INT NOT NULL,
[score] INT,
[average_time_seconds] INT,
[status] VARCHAR(20) NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NOT NULL,
[updated_on] DATETIME NOT NULL,
[updated_by] INT NOT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamAttempts_Exam FOREIGN KEY (exam_id) REFERENCES Exams (id)
);
GO
--================================================
CREATE TABLE dbo.ExamAttemptsAnswer (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[exam_attempt_id] INT NOT NULL,
[question_id] INT NOT NULL,
[date_of_answer] DATETIME NOT NULL,
[answer_duration_seconds] INT NOT NULL,
[student_answer] VARCHAR(MAX),
[doubt] VARCHAR(MAX),
[is_correct] BIT,
[is_visited] BIT DEFAULT (1),
[is_reviewed] BIT DEFAULT (1),
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamAttemptsAnswer_ExamAttempt FOREIGN KEY (exam_attempt_id) REFERENCES ExamAttempts (id),
CONSTRAINT FK_ExamAttemptsAnswer_Question FOREIGN KEY (question_id) REFERENCES Questions (id),
);
GO
--================================================
-- This is required for subjective type questions answer assessement
CREATE TABLE dbo.ExamAttemptsAssessment (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[exam_section_id] INT NOT NULL,
[question_id] INT NOT NULL,
[user_id] INT NOT NULL,
[date_of_answer] DATETIME,
[time_taken_to_answer_in_seconds] INT,
[student_answer] VARCHAR(MAX),
[correctness] VARCHAR(1500),
[assessement] VARCHAR(MAX),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamAttemptsAssessment_ExamSection FOREIGN KEY (exam_section_id) REFERENCES ExamSections (id),
CONSTRAINT FK_ExamAttemptsAssessment_Question FOREIGN KEY (question_id) REFERENCES Questions (id),
CONSTRAINT FK_ExamAttemptsAssessment_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
--================================================
CREATE TABLE dbo.Practices (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[institute_id] INT NOT NULL,
[class_id] INT NOT NULL, -- added
[language_id] INT NOT NULL,
[module] VARCHAR (20) NOT NULL,
[module_id] INT NOT NULL,
[name] VARCHAR (500) NOT NULL,
[instruction] NVARCHAR (1500),
[status] VARCHAR(10) NOT NULL, --draft/published/inactive
[open_datetime] DATETIME,
[complexity] SMALLINT,
[photo] VARCHAR,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_Practices_Institutes FOREIGN KEY (institute_id) REFERENCES Institutes (id),
CONSTRAINT FK_Practices_CreatedByUser FOREIGN KEY (created_by) REFERENCES Users (id),
CONSTRAINT FK_Practices_UpdatedByUser FOREIGN KEY (updated_by) REFERENCES Users (id),
CONSTRAINT FK_Practices_Lang FOREIGN KEY (language_id) REFERENCES Languages (id)
);
GO
CREATE TABLE dbo.PracticeQuestions (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[practice_id] INT NOT NULL,
[question_id] INT NOT NULL,
[duration_seconds] FLOAT,
[question_sequence] SMALLINT,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_PracticeQuestions_Practice FOREIGN KEY (practice_id) REFERENCES Practices (id),
CONSTRAINT FK_PracticeQuestions_Question FOREIGN KEY (question_id) REFERENCES Questions (id)
);
GO
/*
--================================================
CREATE TABLE dbo.ExamPracticeLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[language_id] INT,
[exam_practice_id] INT NOT NULL,
[name] VARCHAR (500) NOT NULL,
[description] NVARCHAR (1500),
[instruction] NVARCHAR (MAX),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamPracticeLanguages_Language FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT FK_ExamPracticeLanguages_ExamPractice FOREIGN KEY (exam_practice_id) REFERENCES ExamPractices (id),
CONSTRAINT UNIQUE_ExamPracticeLanguage UNIQUE (language_id,exam_practice_id,[name])
);
*/
/*
--================================================
CREATE TABLE dbo.ExamQuestionsInPractice (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[practice_id] INT NOT NULL,
[question_id] INT NOT NULL,
[mark] SMALLINT,
[negative_mark] SMALLINT,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamQuestionsInPractice_ExamPractice FOREIGN KEY (practice_id) REFERENCES ExamPractices (id),
CONSTRAINT FK_ExamQuestionsInPractice_Question FOREIGN KEY (question_id) REFERENCES Questions (id)
);
GO
--================================================
CREATE TABLE dbo.ExamPracticeAttempts (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[practice_id] INT NOT NULL,
[user_id] INT NOT NULL,
[attempted_sequence] SMALLINT,
[score] SMALLINT,
[average_time_taken_seconds] INT,
[started_on] DATETIME,
[completed_on] DATETIME,
[status] VARCHAR(20),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ExamPracticeAttempts_ExamPractice FOREIGN KEY (practice_id) REFERENCES ExamPractices (id),
CONSTRAINT FK_ExamPracticeAttempts_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
--================================================
CREATE TABLE dbo.StudentAnswers (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[practice_id] INT NOT NULL,
[question_id] INT NOT NULL,
[user_id] INT NOT NULL,
[answer_date] DATETIME,
[time_taken] SMALLINT,
[comments] VARCHAR(MAX),
[correctness] VARCHAR(MAX),
[student_answer] VARCHAR(MAX),
[student_doubt] VARCHAR(MAX),
[score] SMALLINT,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_StudentAnswers_ExamPractice FOREIGN KEY (practice_id) REFERENCES ExamPractices (id),
CONSTRAINT FK_StudentAnswers_Question FOREIGN KEY (question_id) REFERENCES Questions (id),
CONSTRAINT FK_StudentAnswers_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
*/
/*
--================================================
-- STUDY NOTES
--================================================
CREATE TABLE dbo.StudyNotes (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[subject_id] INT NOT NULL,
[category_id] INT, --if null, Note is for above Subject
[sub_category_id] INT, --if Null, Note is for above Category
[name] VARCHAR(1000),
[description] VARCHAR(MAX),
[pdf_url] VARCHAR(1500),
[video_url] VARCHAR(1500),
[status] VARCHAR(100),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_StudyNotes_User FOREIGN KEY (user_id) REFERENCES Users (id),
CONSTRAINT FK_StudyNotes_Subject FOREIGN KEY (subject_id) REFERENCES Subjects (id),
CONSTRAINT FK_StudyNotes_Category FOREIGN KEY (category_id) REFERENCES Categories (id),
CONSTRAINT FK_StudyNotes_SubCategory FOREIGN KEY (sub_category_id) REFERENCES SubCategories (id),
CONSTRAINT UNIQUE_StudyNotes UNIQUE (user_id,subject_id,category_id,sub_category_id,[name])
);
GO
--================================================
CREATE TABLE dbo.StudyNotesTags (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[study_note_id] INT NOT NULL,
[tag_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_StudyNotesTags_StudyNote FOREIGN KEY (study_note_id) REFERENCES StudyNotes (id),
CONSTRAINT FK_StudyNotesTags_Tag FOREIGN KEY (tag_id) REFERENCES Tags (id)
);
GO
*/
--================================================
-- BOOKMARKS
--================================================
CREATE TABLE dbo.BookmarkedQuestions (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[question_id] INT NOT NULL,
[bookmark_date] DATETIME DEFAULT GETDATE(),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_BookmarkedQuestions_Question FOREIGN KEY (question_id) REFERENCES Questions (id),
CONSTRAINT FK_BookmarkedQuestions_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
--================================================
CREATE TABLE dbo.BookmarkedExams (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[exam_id] INT NOT NULL,
[bookmark_date] DATETIME DEFAULT GETDATE(),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_BookmarkedExams_Exam FOREIGN KEY (exam_id) REFERENCES Exams (id),
CONSTRAINT FK_BookmarkedExams_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
CREATE TABLE dbo.BookmarkedPractices (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[practice_id] INT NOT NULL,
[bookmark_date] DATETIME DEFAULT GETDATE(),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_BookmarkedPractice_Practice FOREIGN KEY (practice_id) REFERENCES ExamPractices (id),
CONSTRAINT FK_BookmarkedPractice_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
/*
--================================================
CREATE TABLE dbo.BookmarkedNotes (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[study_note_id] INT NOT NULL,
[bookmark_date] DATETIME DEFAULT GETDATE(),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_BookmarkedNotes_StudyNote FOREIGN KEY (study_note_id) REFERENCES StudyNotes (id),
CONSTRAINT FK_BookmarkedNotes_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
--===============================================
-- LIBRARY
--================================================
CREATE TABLE dbo.LibraryContents(
[id] INT PRIMARY KEY IDENTITY (1, 1),
[image] IMAGE NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT(1)
);
GO
--================================================
CREATE TABLE dbo.LibraryContentsLanguages (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[library_content_id] INT NOT NULL,
[language_id] INT NOT NULL DEFAULT(1),
[name] NVARCHAR (1500) NOT NULL,
[description] NVARCHAR (MAX) NULL,
[link] VARCHAR (1500) NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_LibraryContentsLanguages_LibraryContents FOREIGN KEY (library_content_id) REFERENCES LibraryContents (id),
CONSTRAINT FK_LibraryContentsLanguages_Language FOREIGN KEY (language_id) REFERENCES Languages (id),
CONSTRAINT UNIQUE_LibraryContentsLanguages UNIQUE (language_id,library_content_id,[name])
);
GO
--================================================
CREATE TABLE dbo.LibraryContentsTags (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[library_content_id] INT NOT NULL,
[tag_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_LibraryContentsTags_LibraryContents FOREIGN KEY (library_content_id) REFERENCES LibraryContents (id),
CONSTRAINT FK_LibraryContentsTags_Tag FOREIGN KEY (tag_id) REFERENCES Tags (id)
);
GO
--================================================
CREATE TABLE dbo.LibraryContentsSubCategories (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[library_content_id] INT NOT NULL,
[sub_category_id] INT NOT NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_LibraryContentsSubCategories_LibraryContents FOREIGN KEY (library_content_id) REFERENCES LibraryContents (id),
CONSTRAINT FK_LibraryContentsSubCategories_SubCategory FOREIGN KEY (sub_category_id) REFERENCES SubCategories (id)
);
GO
*/
--================================================
-- LOG TABLES
--================================================
CREATE TABLE dbo.ActivityLogs (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[action] VARCHAR(100), --Added/created/Appeared/deleted/attempted/cloned
[item_type] VARCHAR(100), --Exam ID, Qns ID, User ID, others
[item] VARCHAR(100),
[action_date] DATETIME,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_ActivityLogs_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
--================================================
CREATE TABLE dbo.UserLogs (
[id] INT PRIMARY KEY IDENTITY (1, 1),
[user_id] INT NOT NULL,
[logged_out_at] DATETIME,
[logged_on_at] DATETIME,
[logged_on_from_ip_addr] VARCHAR(20),
[logged_on_from_latitude] VARCHAR(20),
[logged_on_from_longitude] VARCHAR(20),
[action_date] DATETIME,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_UserLogs_User FOREIGN KEY (user_id) REFERENCES Users (id)
);
GO
--================================================
CREATE TABLE dbo.ContactLogs(
[id] INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[contact_date] DATETIME DEFAULT GETDATE(),
[contact_by] VARCHAR(150) NOT NULL,
[contact_for] VARCHAR(150) NOT NULL,
[email_to] VARCHAR(150) NOT NULL,
[email_from] VARCHAR(150) NOT NULL,
[email_subject] VARCHAR(250) NOT NULL,
[email_message] VARCHAR(MAX) NOT NULL,
[reply_date] DATETIME,
[reply_by] INT,
[comment] VARCHAR(max),
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1)
);
GO
--================================================
CREATE TABLE dbo.ErrorLogs(
[id] INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[error_date] [datetime] NULL,
[ticket_no] [varchar](max) NULL,
[environment] [varchar](max) NULL,
[error_page] [varchar](max) NULL,
[error_message] [varchar](max) NULL,
[error_inner_message] [varchar](max) NULL,
[error_call_stack] [varchar](max) NULL,
[user_domain] [varchar](max) NULL,
[language] [varchar](max) NULL,
[target_site] [varchar](max) NULL,
[the_class] [varchar](max) NULL,
[user_agent] [varchar](max) NULL,
[type_log] [varchar](max) NULL,
[user_id] [int] NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1)
);
GO
--================================================
CREATE TABLE dbo.PasswordReset(
[id] INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
[generated_for_user_id] INT NOT NULL,
[auth_key] UNIQUEIDENTIFIER NOT NULL,
[is_expired] CHAR(1) DEFAULT('N'),
[reseted_on] DATETIME NULL,
[created_on] DATETIME DEFAULT GETDATE(),
[created_by] INT NULL,
[updated_on] DATETIME,
[updated_by] INT NULL,
[is_active] BIT DEFAULT (1),
CONSTRAINT FK_PasswordReset_User FOREIGN KEY (generated_for_user_id) REFERENCES Users (id)
);
GO
--<<<=============================================>>>
ALTER TABLE dbo.Subscriptions
ADD CONSTRAINT FK_Subscriptions_Institute FOREIGN KEY (institute_id)
REFERENCES Institutes(id);
GO