DBScript.sql

CREATE TABLE GLOBAL_APP_COUNTRIES (
    ID int NOT NULL Identity,
    COUNTRY_NAME varchar(100) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE GLOBAL_APP_CMP_ZONE (
    ID int NOT NULL Identity,
    CMP_ZONE_NAME varchar(100) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE GLOBAL_APP_ORG_MASTER (
    ID int NOT NULL Identity,
    ORG_NAME varchar(100) NOT NULL,
	ORG_URL varchar(100),
	ACC_STATUS varchar(100),
	ACC_TRIAL_EXPIRED bit,
    ORG_COUNTRY_ID int,
	CMP_ZONE_ID int,
	PARENT_ORG_ID int,
	ONBOARD_DATE DATETIME,
    PRIMARY KEY (ID),
    CONSTRAINT FK_ORG_COUNTRY_ID FOREIGN KEY (ORG_COUNTRY_ID) REFERENCES GLOBAL_APP_COUNTRIES(ID),
	CONSTRAINT FK_CMP_ZONE_ID FOREIGN KEY (CMP_ZONE_ID) REFERENCES GLOBAL_APP_CMP_ZONE(ID),
	CONSTRAINT FK_PARENT_ORG_ID FOREIGN KEY (PARENT_ORG_ID) REFERENCES GLOBAL_APP_ORG_MASTER(ID)
);

CREATE TABLE GLOBAL_APP_USERS (
    ID int NOT NULL Identity,
    USER_NAME varchar(100) NOT NULL,
	USER_EMAIL varchar(100),
    USER_PASSCODE varchar(100),
	PASSCODE_CHANGED varchar(100),
	ORG_ID int NOT NULL,
	ACTIVE_STATUS varchar(100),
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
    PRIMARY KEY (ID),
    CONSTRAINT FK_USERS_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_USERS_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_USERS_ORG_ID FOREIGN KEY (ORG_ID) REFERENCES GLOBAL_APP_ORG_MASTER(ID)
);

CREATE TABLE GLOBAL_APP_ROLE_ACTIVITIES (
    ID int NOT NULL Identity,
    ACTIVITY_NAME varchar(100) NOT NULL,
	ACTIVITY_DESC varchar(100),
	ORG_ID int,
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
    PRIMARY KEY (ID),
	CONSTRAINT FK_ROLE_ACTIVITIES_ORG_ID FOREIGN KEY (ORG_ID) REFERENCES GLOBAL_APP_ORG_MASTER(ID),
    CONSTRAINT FK_ROLE_ACTIVITIES_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_ROLE_ACTIVITIES_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID)
);

CREATE TABLE GLOBAL_APP_ROLES (
    ID int NOT NULL Identity,
    ROLE_NAME varchar(100) NOT NULL,
	ROLE_DESC varchar(100),
	ORG_ID int,
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
    PRIMARY KEY (ID),
	CONSTRAINT FK_ROLES_ORG_ID FOREIGN KEY (ORG_ID) REFERENCES GLOBAL_APP_ORG_MASTER(ID),
    CONSTRAINT FK_ROLES_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_ROLES_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID)
);

CREATE TABLE GLOBAL_APP_ROLE_ACTIVITY_ACCESS (
    ID int NOT NULL Identity,
	ROLE_ID int,
	ACTIVITY_ID int,
	STATUS varchar(100),
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
    PRIMARY KEY (ID),
	CONSTRAINT FK_ROLE_ACTIVITY_ACCESS_ROLE_ID FOREIGN KEY (ROLE_ID) REFERENCES GLOBAL_APP_ROLES(ID),
	CONSTRAINT FK_ROLE_ACTIVITY_ACCESS_ACTIVITY_ID FOREIGN KEY (ACTIVITY_ID) REFERENCES GLOBAL_APP_ROLE_ACTIVITIES(ID),
    CONSTRAINT FK_ROLE_ACTIVITY_ACCESS_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_ROLE_ACTIVITY_ACCESS_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID)
);


CREATE TABLE GLOBAL_APP_ACCESS (
    ID int NOT NULL Identity,
    USER_ID int NOT NULL,
	ROLE_ID int NOT NULL,
	STATUS varchar(100),
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
    PRIMARY KEY (ID),
    CONSTRAINT FK_ACCESS_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_ACCESS_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_ACCESS_USER_ID FOREIGN KEY (USER_ID) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_ACCESS_ROLE_ID FOREIGN KEY (ROLE_ID) REFERENCES GLOBAL_APP_ROLES(ID)
);

CREATE TABLE TOKEN_TYPE (
    ID int NOT NULL Identity,
    [TYPE] varchar(100),
	DESCRPTION varchar(100),
    PRIMARY KEY (ID)
);

CREATE TABLE REGISTRATION_TOKEN (
    ID int NOT NULL Identity,
    USER_ID int,
	TOKEN varchar(200),
	EMAIL varchar(100),
	TOKEN_TYPE_ID int,
	CREATED_BY int,
	CREATED_ON DATETIME,
	IS_VALID bit,
    PRIMARY KEY (ID),
	CONSTRAINT FK_REGISTRATION_TOKEN_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_REGISTRATION_TOKEN_TOKEN_TYPE_ID FOREIGN KEY (TOKEN_TYPE_ID) REFERENCES TOKEN_TYPE(ID)
);

-----------------------------------------------------------------

CREATE TABLE GLOSSARY_MASTER (
    ID int NOT NULL Identity,
    ORG_ID int,
	GLOSS_NAME varchar(200),
	GLOSS_OWNER int,
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
	DELETED bit,
    PRIMARY KEY (ID),
	CONSTRAINT FK_GLOSSARY_MASTER_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_GLOSSARY_MASTER_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_GLOSSARY_MASTER_ORG_ID FOREIGN KEY (ORG_ID) REFERENCES GLOBAL_APP_ORG_MASTER(ID),
	CONSTRAINT FK_GLOSSARY_MASTER_GLOSS_OWNER FOREIGN KEY (GLOSS_OWNER) REFERENCES GLOBAL_APP_USERS(ID)
);

CREATE TABLE GLOSSARY_DETAILS (
    ID int NOT NULL Identity,
    GLOSS_ID int,
	GLOSS_DESC varchar(2000),
	GLOSS_LABEL varchar(200),
	GLOSS_STATUS varchar(200),
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
    PRIMARY KEY (ID),
	CONSTRAINT FK_GLOSSARY_DETAILS_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_GLOSSARY_DETAILS_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_GLOSSARY_DETAILS_GLOSS_ID FOREIGN KEY (GLOSS_ID) REFERENCES GLOSSARY_MASTER(ID)
);

CREATE TABLE GLOBAL_TERM_STATUS (
    ID int NOT NULL Identity,
    STATUS_NAME varchar(100) NOT NULL,
	STATUS_DESC varchar(100),
    PRIMARY KEY (ID)
);

CREATE TABLE TERMS (
    ID int NOT NULL Identity,
    GLOSS_DETAIL_ID int,
	TERM_NAME varchar(200),
	TERM_DESC varchar(2000),
	TERM_DEF varchar(2000),
	BUSINESS_RULES varchar(2000),
	DATA_OWNER int,
	RELATED_TERMS varchar(2000),
	IS_PII bit,
	CRITICAL_DATA_ELEMENT bit,
	TERM_LABEL varchar(2000),
	TERM_STATUS int,
	CREATED_BY int,
	CREATED_ON DATETIME,
	UPDATED_BY int,
	UPDATED_ON DATETIME,
	DELETED bit,
    PRIMARY KEY (ID),
	CONSTRAINT FK_TERMS_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_TERMS_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_TERMS_DATA_OWNER FOREIGN KEY (DATA_OWNER) REFERENCES GLOBAL_APP_USERS(ID),
	CONSTRAINT FK_TERMS_TERM_STATUS FOREIGN KEY (TERM_STATUS) REFERENCES GLOBAL_TERM_STATUS(ID),
	CONSTRAINT FK_TERMS_GLOSS_DETAIL_ID FOREIGN KEY (GLOSS_DETAIL_ID) REFERENCES GLOSSARY_DETAILS(ID)
);

INSERT INTO [dbo].[GLOBAL_TERM_STATUS]
           ([STATUS_NAME]
           ,[STATUS_DESC])
     VALUES
           ('PROPOSED',
           'PROPOSED'),
		   ('WAITING_APPROVAL',
           'WAITING APPROVAL'),
		   ('APPROVED',
           'APPROVED'),
		   ('PUBLISHED',
           'PUBLISHED')

INSERT INTO [dbo].[GLOBAL_APP_ROLES]
           ([ROLE_NAME]
           ,[ROLE_DESC])
     VALUES
           ('Data-Steward',
           'Data-Steward'),
		   ('Admin',
           'Admin'),
		   ('Data-Admin',
           'Data-Admin')


INSERT INTO [dbo].[GLOBAL_APP_CMP_ZONE]
           ([CMP_ZONE_NAME])
     VALUES
           ('Asia'),
		   ('Africa'),
		   ('Europe'),
		   ('Australia'),
		   ('North America'),
		   ('South America'),
		   ('Antarctica')

INSERT INTO [dbo].[GLOBAL_APP_COUNTRIES]
           ([COUNTRY_NAME])
     VALUES
           ('India'),
		   ('United Kingdom'),
		   ('United State'),
		   ('Russia'),
		   ('Norve'),
		   ('Itly'),
		   ('Japan'),
		   ('Germany'),
		   ('Mexico'),
		   ('Franse');

INSERT INTO [dbo].[TOKEN_TYPE]
           ([TYPE],
		   DESCRPTION)
     VALUES
           ('R','REGISTRATION'),
		   ('E','REGISTRATION-EXPIRE'),
		   ('A','ADD-USER'),
		   ('F','FORGOT-PASSWORD');
		   
INSERT INTO [dbo].[GLOBAL_APP_ROLE_ACTIVITIES]
           ([ACTIVITY_NAME]
           ,[ACTIVITY_DESC])
     VALUES
           ('CREATE',
           'CREATE'),
		   ('DELETE',
           'DELETE'),
		   ('UPDATE',
           'UPDATE');