USE larkoatest

CREATE TABLE Address
(
	AddressID uniqueidentifier NOT NULL PRIMARY KEY,  AddressLine nvarchar(100) NULL, AddressLine2 nvarchar(100) NULL,
	ForeignAddressLine nvarchar(250) NULL,            City nvarchar(50) NULL,         State nvarchar(50) NULL, 
	Country nvarchar(50) NULL,                        PostalCode nvarchar(50) NULL
) 

CREATE TABLE ContactInformation
(
	ContactInformationID uniqueidentifier NOT NULL PRIMARY KEY, Phone nvarchar(50) NULL, MobileTelephone nvarchar(50) NULL,
	Fax nvarchar(50) NULL,                                      EMail nvarchar(50) NULL, IM nvarchar(50) NULL
)

CREATE TABLE DataDictionary
(
	ID uniqueidentifier NOT NULL PRIMARY KEY, DataValue1 nvarchar(50) NULL, DataValue2 nvarchar(50) NULL,
	DataValue3 nvarchar(50) NOT NULL,         DataValue4 nvarchar(50) NULL, DataValue5 nvarchar(50) NULL,
	SystemPreset bit NOT NULL,                DataType int NOT NULL,        DefaultValue bit NOT NULL DEFAULT 0
)

CREATE TABLE Department
(
	DepartmentID uniqueidentifier NOT NULL PRIMARY KEY, ParentID uniqueidentifier NOT NULL, DepartmentName nvarchar(100) NOT NULL,
	SortNumber int NOT NULL DEFAULT 100,                Remarks nvarchar(250) NULL,         DepartmentStatus int NOT NULL DEFAULT 1
)

CREATE TABLE Company
(
	CompanyID uniqueidentifier NOT NULL PRIMARY KEY,                               CompanyName nvarchar(250) NOT NULL, CompanyForeignName nvarchar(250) NULL,
	AddressID uniqueidentifier NOT NULL FOREIGN KEY REFERENCES Address(AddressID), Phone nvarchar(50) NULL,            Fax nvarchar(50) NULL,
	TaxpayerID nvarchar(50) NULL,                                                  DepositBank nvarchar(100) NULL,     AccountNumber nvarchar(50) NULL,
	WebSite nvarchar(100) NULL,                                                    EMail nvarchar(50) NULL,            OurSide bit NOT NULL DEFAULT 0,
	IsClient bit NOT NULL DEFAULT 0,                                               IsSupplier bit NOT NULL DEFAULT 0
)

CREATE TABLE EndUser
(
	EndUserID uniqueidentifier NOT NULL PRIMARY KEY,                                        EndUserCode nvarchar(50) NOT NULL UNIQUE,                                       EndUserTypeID uniqueidentifier NOT NULL FOREIGN KEY REFERENCES DataDictionary(ID),
	FirstName nvarchar(50) NULL,                                                            LastName nvarchar(50) NULL,                                                     ForeignName nvarchar(100) NULL,
	Gender int NOT NULL,                                                                    AddressID  uniqueidentifier NOT NULL FOREIGN KEY REFERENCES Address(AddressID), ContactInformationID uniqueidentifier NOT NULL FOREIGN KEY REFERENCES ContactInformation(ContactInformationID),
	Password nvarchar(50) NOT NULL,                                                         EndUserStatus int NOT NULL DEFAULT 1,                                           IsOAUser bit NOT NULL DEFAULT 1,
	DepartmentID uniqueidentifier NOT NULL FOREIGN KEY REFERENCES Department(DepartmentID), SystemPreset bit NOT NULL DEFAULT 0
)

CREATE TABLE FormGroup
{
	FormGroupID uniqueidentifier NOT NULL PRIMARY KEY, FormGroupName nvarchar(100) NOT NULL, SystemPreset bit NOT NULL DEFAULT 0
}

