AipGateway/Database/HR 테이블 스크립트.SQL

CREATE TABLE aipdb.dbo.TB_HR_USER (
	UserID varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	DisplayName varchar(255) COLLATE Korean_Wansung_CI_AS NULL,
	DisplayName2 varchar(255) DEFAULT getdate() NOT NULL,
	DisplayName3 varchar(255) NULL,
	DisplayName4 varchar(255) NULL,
	Mail varchar(255) NULL,
	Manager varchar(255) NULL,
	EmployeeID varchar(255) NULL,
	TelephoneNumber varchar(255) NULL,
	Mobile varchar(255) NULL,
	PositionID varchar(30) NULL,
	WorkingName varchar(255) NULL,
	WorkPlace varchar(255) NULL,
	LeaveDate DateTime NULL,
	Birth DateTime NULL,
	BirthType bit NULL,
	EnterDate DateTime NULL,
	Gitgo_License varchar(255) NULL,
	M365_License varchar(255) NULL,
	UseFlag varchar(1) NULL,
	CustomManager varchar(255) NULL,
	CONSTRAINT PK_HR_USER PRIMARY KEY (UserID)
);


CREATE TABLE aipdb.dbo.TB_HR_GROUP (
	DeptID varchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
	DisplayName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	UpperDeptID varchar(30) DEFAULT getdate() NOT NULL,
	Mail varchar(255) NULL,
	UseFlag varchar(1) NULL,
	Sort 		int NULL,
	CONSTRAINT PK_HR_GROUP PRIMARY KEY (DeptID)
);


CREATE TABLE aipdb.dbo.TB_HR_GROUP_USER (
	UserID varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	DeptID varchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
	TitleID varchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
	IsMainDept varchar(1) NULL,
	CONSTRAINT PK_HR_GROUP_USER PRIMARY KEY (UserID, DeptID)
);


CREATE TABLE aipdb.dbo.TB_HR_USER_TITLE (
	TitleID varchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
	DisplayName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	Sort 		int NULL,
	CONSTRAINT PK_HR_USER_TITLE PRIMARY KEY (TitleID)
);


CREATE TABLE aipdb.dbo.TB_HR_USER_GRADE (
	PositionID varchar(30) COLLATE Korean_Wansung_CI_AS NOT NULL,
	DisplayName varchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
	Sort 		int NULL,
	CONSTRAINT PK_HR_USER_GRADE PRIMARY KEY (PositionID)
);