123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248 |
- CREATE TABLE CmBusinessField
- (
- SiteId int NOT NULL ,
- BusinessFieldId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , BusinessFieldId ASC)
- );
- DBCC CHECKIDENT( CmBusinessField, RESEED, 1 )
- SET IDENTITY_INSERT CmBusinessField ON
- INSERT INTO CmBusinessField (SiteId, BusinessFieldId, Name, IsUse) VALUES (1, 1, '공통', 1);
- SET IDENTITY_INSERT CmBusinessField OFF
- CREATE TABLE CmPosition
- (
- SiteId int NOT NULL ,
- PositionId int identity(1,1) NOT NULL ,
- Name nvarchar(40) NOT NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , PositionId ASC)
- );
- DBCC CHECKIDENT( CmPosition, RESEED, 1 )
- CREATE TABLE CmUser
- (
- SiteId int NOT NULL ,
- UserId nvarchar(24) NOT NULL ,
- CompanyId int NULL ,
- DepartmentId int NULL ,
- PositionId int NULL ,
- BusinessFieldId int NULL ,
- UserGroupId int NULL ,
- Name nvarchar(30) NOT NULL ,
- Passwd nvarchar(30) NOT NULL ,
- Birthday datetime NULL ,
- Lunar bit NULL ,
- StateId int NULL ,
- EmploymentType int NULL ,
- EmploymentStatus int NULL ,
- OfficePhoneNo nvarchar(20) NULL ,
- HomePhoneNo nvarchar(20) NULL ,
- MobilePhoneNo nvarchar(20) NULL ,
- HomeZip1 CHAR(3) NULL ,
- HomeZip2 CHAR(3) NULL ,
- HomeAddress1 nvarchar(50) NULL ,
- HomeAdderss2 nvarchar(100) NULL ,
- Email nvarchar(50) NULL ,
- EnterDate datetime NULL ,
- RetireDate datetime NULL ,
- Comment nvarchar(1024) NULL ,
- Rfcsn nvarchar(30) NULL ,
- PasswordUpdatedDate datetime NULL ,
- PhotoFileId int NULL ,
- IsBems bit NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC , UserId ASC)
- );
- INSERT INTO CmUser (SiteId, BusinessFieldId, UserId, Name, Passwd) VALUES (1, 1, 'admin', 'Administrator', 'admin');
- CREATE TABLE FmsLicense
- (
- SiteId int NOT NULL,
- LicenseId int identity(1,1) NOT NULL,
- Name nvarchar(52) NOT NULL,
- IssuingOffice nvarchar(52) NOT NULL,
- BusinessFieldId int NULL,
- EducationalInstitute nvarchar(52) NULL,
- Note nvarchar(256) NULL,
- IsUse bit NULL,
- PRIMARY KEY (SiteId ASC , LicenseId ASC)
- );
- DBCC CHECKIDENT( FmsLicense, RESEED, 1 )
- CREATE TABLE CmUserToLicense
- (
- SiteId int NOT NULL,
- UserId nvarchar(24) NOT NULL ,
- LicenseId int NOT NULL,
- PRIMARY KEY (SiteId ASC , UserId ASC , LicenseId ASC)
- );
- ALTER TABLE CmPosition
- ADD CONSTRAINT FK__CmUserPosition__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE CmUser
- ADD CONSTRAINT FK__CmUser__CmDepartment
- FOREIGN KEY (SiteId , CompanyId , DepartmentId)
- REFERENCES CmDepartment(SiteId , CompanyId , DepartmentId)
- ;
- ALTER TABLE CmUser
- ADD CONSTRAINT FK__CmUser__CmCompany
- FOREIGN KEY (SiteId , CompanyId) REFERENCES CmCompany(SiteId , CompanyId)
- ;
- ALTER TABLE CmUser
- ADD CONSTRAINT FK__CmUser__CmPosition
- FOREIGN KEY (SiteId , PositionId)
- REFERENCES CmPosition(SiteId , PositionId)
- ;
- ALTER TABLE CmUser
- ADD CONSTRAINT FK__CmUser__CmBusinessField
- FOREIGN KEY (SiteId , BusinessFieldId) REFERENCES CmBusinessField(SiteId , BusinessFieldId)
- ;
- ALTER TABLE FmsLicense
- ADD CONSTRAINT FK__FmsLicense__CmSite
- FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE FmsLicense
- ADD CONSTRAINT FK__FmsLicense__CmBusinessField
- FOREIGN KEY (SiteId,BusinessFieldId) REFERENCES CmBusinessField(SiteId, BusinessFieldId)
- ;
- ALTER TABLE CmUserToLicense
- ADD CONSTRAINT FK__CmUserToLicense__CmUser
- FOREIGN KEY (SiteId,UserId) REFERENCES CmUser(SiteId,UserId)
- ;
- ALTER TABLE CmUserToLicense
- ADD CONSTRAINT FK__CmUserToLicense__FmsLicense
- FOREIGN KEY (SiteId,LicenseId) REFERENCES FmsLicense(SiteId,LicenseId)
- ;
|