123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153 |
- CREATE TABLE CmSite
- (
- SiteId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- AddressZip1 nchar(3) NULL ,
- AddressZip2 nchar(3) NULL ,
- Address1 nvarchar(100) NULL ,
- Adderss2 nvarchar(100) NULL ,
- PhoneNo nvarchar(20) NULL ,
- Uri nvarchar(200) NULL ,
- Note nvarchar(1024) NULL ,
- IsUse bit NULL ,
-
- PRIMARY KEY (SiteId ASC)
- );
- DBCC CHECKIDENT( CmSite, RESEED, 1 )
- CREATE TABLE CmCompany
- (
- SiteId int NOT NULL ,
- CompanyId int identity(1,1) NOT NULL ,
- CompanyTypeId int NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- PhoneNo nvarchar(20) NOT NULL ,
- FaxNo nvarchar(20) NULL ,
- RepresentativeName nvarchar(20) NOT NULL ,
- SalesName nvarchar(20) NULL ,
- SalesPhoneNo nvarchar(20) NULL ,
- AddressZip1 nchar(3) NULL ,
- AddressZip2 nchar(3) NULL ,
- Address1 nvarchar(50) NULL ,
- Adderss2 nvarchar(100) NULL ,
- HomepageUri nvarchar(100) NULL ,
- Note nvarchar(1024) NULL ,
- IsUse bit NULL ,
- PRIMARY KEY (SiteId ASC , CompanyId ASC)
- );
- DBCC CHECKIDENT( CmCompany, RESEED, 1 )
- CREATE TABLE CmCompanyType
- (
- SiteId int NOT NULL ,
- CompanyTypeId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- IsUse bit NULL ,
- PRIMARY KEY (SiteId ASC , CompanyTypeId ASC)
- );
- DBCC CHECKIDENT( CmCompanyType, RESEED, 1 )
- CREATE TABLE CmDepartment
- (
- SiteId int NOT NULL ,
- CompanyId int NOT NULL ,
- DepartmentId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- ShortName nvarchar(50) NULL ,
- FaxNo nvarchar(20) NULL ,
- PhoneNo nvarchar(20) NULL ,
- Note nvarchar(1024) NULL ,
- IsUse bit NULL ,
- PRIMARY KEY (SiteId ASC , CompanyId ASC , DepartmentId ASC)
- );
- DBCC CHECKIDENT( CmDepartment, RESEED, 1 )
- ALTER TABLE CmCompany
- ADD CONSTRAINT FK__CmCompany__CmSite FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE CmCompany
- ADD CONSTRAINT FK__CmCompany__CmCompanyType FOREIGN KEY (SiteId,CompanyTypeId) REFERENCES CmCompanyType(SiteId,CompanyTypeId)
- ;
- ALTER TABLE CmDepartment
- ADD CONSTRAINT FK__CmDepartment__CmCompany FOREIGN KEY (SiteId,CompanyId) REFERENCES CmCompany(SiteId,CompanyId)
- ;
|