4JN30DBy

1Create table - Mysql
2CREATE  TABLE IF NOT EXISTS `axis`.`Employee` (
3`idEmployee` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
4`idAccount` INT(10) UNSIGNED NOT NULL ,
5`user` VARCHAR(45) NULL DEFAULT NULL ,
6`pass` VARCHAR(45) NULL DEFAULT NULL ,
7`firstName` VARCHAR(45) NULL DEFAULT NULL ,
8`lastName` VARCHAR(45) NULL DEFAULT NULL ,
9`middleName` VARCHAR(45) NULL DEFAULT NULL ,
10`idGrp` INT(10) UNSIGNED NULL DEFAULT NULL ,
11`idCompany` INT(10) UNSIGNED NULL DEFAULT NULL ,
12`idLocation` INT(10) UNSIGNED NULL DEFAULT NULL ,
13`idUnit` INT(10) UNSIGNED NULL DEFAULT NULL ,
14`idCrew` INT(10) UNSIGNED NULL DEFAULT NULL ,
15`idPosition` INT(10) UNSIGNED NULL DEFAULT NULL ,
16`officeLoc` VARCHAR(45) NULL DEFAULT NULL ,
17`wPhone` VARCHAR(12) NULL DEFAULT NULL ,
18`wCell` VARCHAR(12) NULL DEFAULT NULL ,
19`wFax` VARCHAR(12) NULL DEFAULT NULL ,
20`wEmail` VARCHAR(45) NULL DEFAULT NULL ,
21`radio` VARCHAR(12) NULL DEFAULT NULL ,
22`hPhone` VARCHAR(12) NULL DEFAULT NULL ,
23`hCell` VARCHAR(12) NULL DEFAULT NULL ,
24`hEmail` VARCHAR(45) NULL DEFAULT NULL ,
25`addrOne` VARCHAR(45) NULL DEFAULT NULL ,
26`addrTwo` VARCHAR(45) NULL DEFAULT NULL ,
27`city` VARCHAR(45) NULL DEFAULT NULL ,
28`state` VARCHAR(45) NULL DEFAULT NULL ,
29`zip` VARCHAR(10) NULL DEFAULT NULL ,
30`emergContact` VARCHAR(45) NULL DEFAULT NULL ,
31`emergPhone` VARCHAR(12) NULL DEFAULT NULL ,
32`gender` ENUM('male','female') NULL DEFAULT NULL ,
33`birthDate` DATE NULL DEFAULT NULL ,
34`ssn` VARCHAR(5) NULL DEFAULT NULL ,
35`hireDate` DATE NULL DEFAULT NULL ,
36`sepDate` DATE NULL DEFAULT NULL ,
37`comment` VARCHAR(255) NULL DEFAULT NULL ,
38`photo` MEDIUMBLOB NULL DEFAULT NULL ,
39`isActive` BIT(1) NOT NULL DEFAULT 1 ,
40`allowLogin` BIT(1) NOT NULL DEFAULT 0 ,
41`trackTravel` BIT(1) NOT NULL DEFAULT 1 ,
42`trackTimesheet` BIT(1) NOT NULL DEFAULT 1 ,
43`defFltArr` VARCHAR(10) NULL DEFAULT NULL ,
44`defFltDep` VARCHAR(10) NULL DEFAULT NULL ,
45`defDayTimeStart` TIME NOT NULL DEFAULT '06:00:00' ,
46`defHoursPerDay` DECIMAL(4,2) NOT NULL DEFAULT '11.50' ,
47`userType` ENUM('root','admin','view','acl','report','employee') NOT NULL DEFAULT 'employee',
48`created` DATETIME NOT NULL ,
49`modified` TIMESTAMP NOT NULL ,
50`modifiedBy` INT(10) UNSIGNED NULL DEFAULT NULL ,
51PRIMARY KEY (`idEmployee`, `idAccount`, `idGrp`, `idCompany`, `idLocation`, `idUnit`, `idCrew`, `idPosition`, `modifiedBy`) ,
52UNIQUE INDEX `UNIQUE` (`idEmployee` ASC) ,
53INDEX `fk_Employee_Grp` (`idGrp` ASC) ,
54INDEX `fk_Employee_Company` (`idCompany` ASC) ,
55INDEX `fk_Employee_Unit` (`idUnit` ASC) ,
56INDEX `fk_Employee_Location` (`idLocation` ASC) ,
57INDEX `fk_Employee_Crew` (`idCrew` ASC) ,
58INDEX `fk_Employee_Position` (`idPosition` ASC) ,
59INDEX `fk_Employee_Employee` (`modifiedBy` ASC) ,
60CONSTRAINT `fk_Employee_Grp`
61FOREIGN KEY (`idGrp` )
62REFERENCES `axis`.`Grp` (`idGrp` )
63ON DELETE SET NULL
64ON UPDATE CASCADE,
65CONSTRAINT `fk_Employee_Company`
66FOREIGN KEY (`idCompany` )
67REFERENCES `axis`.`Company` (`idCompany` )
68ON DELETE SET NULL
69ON UPDATE CASCADE,
70CONSTRAINT `fk_Employee_Unit`
71FOREIGN KEY (`idUnit` )
72REFERENCES `axis`.`Unit` (`idUnit` )
73ON DELETE SET NULL
74ON UPDATE CASCADE,
75CONSTRAINT `fk_Employee_Location`
76FOREIGN KEY (`idLocation` )
77REFERENCES `axis`.`Location` (`idLocation` )
78ON DELETE SET NULL
79ON UPDATE CASCADE,
80CONSTRAINT `fk_Employee_Crew`
81FOREIGN KEY (`idCrew` )
82REFERENCES `axis`.`Crew` (`idCrew` )
83ON DELETE SET NULL
84ON UPDATE CASCADE,
85CONSTRAINT `fk_Employee_Position`
86FOREIGN KEY (`idPosition` )
87REFERENCES `axis`.`Position` (`idPosition` )
88ON DELETE SET NULL
89ON UPDATE CASCADE,
90CONSTRAINT `fk_Employee_Employee`
91FOREIGN KEY (`modifiedBy` )
92REFERENCES `axis`.`Employee` (`idEmployee` )
93ON DELETE SET NULL
94ON UPDATE CASCADE)
95ENGINE = InnoDB
96DEFAULT CHARACTER SET = utf8
97COLLATE = utf8_general_ci
98	
99`idGrp` INT(10) UNSIGNED NULL DEFAULT NULL`
100	
101PRIMARY KEY (`idEmployee`, `idAccount`, `idGrp`, ... )
102	
103CONSTRAINT `fk_Employee_Grp` FOREIGN KEY (`idGrp` ) 
104 REFERENCES `axis`.`Grp` (`idGrp` ) ON DELETE SET NULL ON UPDATE CASCADE