Viewing file: mo_ealumni_prototype.sql (19.03 KB) -rw-r--r-- Select action/file-type: (+) | (+) | (+) | Code (+) | Session (+) | (+) | SDB (+) | (+) | (+) | (+) | (+) | (+) |
-- สร้างฐานศิษย์เก่า
-- CREATE DATABASE `registealumni` ;
--
-- โครงสร้างตาราง `AlumniBio`
--
CREATE TABLE `AlumniBio` (
`alumniId` int(11) NOT NULL default '0',
`studentId` int(10) NOT NULL default '0',
`nationId` int(2) default NULL,
`religionId` int(1) default NULL,
`bloodGroup` varchar(6) NOT NULL,
`birthDate` date NOT NULL default '0000-00-00',
`birthCntId` int(4) NOT NULL,
`birthProvinceId` int(2) default NULL,
`homeAddress` varchar(100) NOT NULL,
`homeDistrictId` int(2) default NULL,
`homeAmphurId` int(2) default NULL,
`homeProvinceId` int(2) default NULL,
`homeZipcode` varchar(5) NOT NULL,
`homePhoneNo` varchar(10) NOT NULL,
`officeName` varchar(100) NOT NULL,
`workCntId` int(4) NOT NULL default '0',
`officeAddress` varchar(100) NOT NULL,
`officeDistrictId` int(2) default NULL,
`officeAmphurId` int(2) default NULL,
`officeProvinceId` int(2) default NULL,
`officeZipcode` varchar(5) NOT NULL,
`officePhoneNo` varchar(10) NOT NULL,
`workingStatus` char(1) NOT NULL,
`workingPosition` varchar(100) NOT NULL,
`workingSalary` float NOT NULL default '0',
`fatherName` varchar(32) NOT NULL,
`fatherAddress` varchar(100) NOT NULL,
`fatherDistrictId` int(2) default NULL,
`fatherAmphurId` int(2) default NULL,
`fatherProvinceId` int(2) default NULL,
`fatherZipcode` varchar(5) NOT NULL,
`fatherPhoneNo` varchar(10) NOT NULL,
`fatherOccupation` varchar(100) NOT NULL,
`fatherStatus` int(1) NOT NULL default '0',
`motherName` varchar(32) NOT NULL,
`motherAddress` varchar(100) NOT NULL,
`motherDistrictId` int(2) default NULL,
`motherAmphurId` int(2) default NULL,
`motherProvinceId` int(2) default NULL,
`motherZipcode` varchar(5) NOT NULL,
`motherPhoneNo` varchar(10) NOT NULL,
`motherOccupation` varchar(100) NOT NULL,
`motherStatus` int(1) NOT NULL default '0',
`studentSex` char(1) NOT NULL,
`parentName` varchar(32) NOT NULL,
`parentRelation` varchar(16) NOT NULL,
`parentAddress` varchar(100) NOT NULL,
`parentDistrictId` int(2) default NULL,
`parentAmphurId` int(2) default NULL,
`parentProvinceId` int(2) default NULL,
`parentZipcode` varchar(5) NOT NULL,
`parentPhoneNo` varchar(10) NOT NULL,
`parentMobile` varchar(10) default NULL,
`parentOcc` varchar(50) NOT NULL,
`parentIncome` float NOT NULL default '0',
`parentEmail` varchar(32) NOT NULL,
`contactPerson` varchar(32) NOT NULL,
`contactAddress` varchar(100) NOT NULL,
`contactDistrictId` int(2) default NULL,
`contactAmphurId` int(2) default NULL,
`contactProvinceId` int(2) default NULL,
`contactZipcode` varchar(5) NOT NULL,
`contactPhoneNo` varchar(10) NOT NULL,
`cardExpiryDate` date NOT NULL default '0000-00-00',
`currentCntId` int(4) NOT NULL default '0',
`currentAddress` varchar(100) NOT NULL,
`currentDistrictId` int(2) default NULL,
`currentAmphurId` int(2) default NULL,
`currentProvinceId` int(2) default NULL,
`currentZipcode` varchar(5) NOT NULL,
`currentPhoneNo` varchar(10) NOT NULL,
`graduateAddress` varchar(100) NOT NULL,
`graduateDistrictId` int(2) default NULL,
`graduateAmphurId` int(2) default NULL,
`graduateProvinceId` int(2) default NULL,
`graduateZipcode` varchar(5) NOT NULL,
`graduatePhoneNo` varchar(10) NOT NULL,
`maritalStatusId` int(1) default NULL,
`weight` float NOT NULL default '0',
`height` float NOT NULL default '0',
`picturePath` varchar(100) NOT NULL default 'photo.jpg',
`recruitmentTypeId` int(1) default NULL,
`occExamResult` varchar(10) NOT NULL,
`canRefund` char(1) NOT NULL default 'N',
`oldStudentName` varchar(32) default NULL,
`entryDegree` int(11) default NULL,
`healthPrivId` int(11) default NULL,
`o1prefixId` int(3) NOT NULL,
`o1fName` varchar(50) NOT NULL,
`o1lName` varchar(50) NOT NULL,
`o2prefixId` int(3) NOT NULL,
`o2fName` varchar(50) NOT NULL,
`o2lName` varchar(50) NOT NULL,
`regPrefix` int(4) default NULL,
`regStdName` varchar(100) default NULL,
`regStdSurname` varchar(100) default NULL,
`regStdNameE` varchar(100) default NULL,
`regStdSurnameE` varchar(100) default NULL,
PRIMARY KEY (`alumniId`),
KEY `nationId` (`nationId`),
KEY `religionId` (`religionId`),
KEY `birthProvinceId` (`birthProvinceId`),
KEY `homeDistrictId` (`homeDistrictId`),
KEY `homeAmphurId` (`homeAmphurId`),
KEY `homeProvinceId` (`homeProvinceId`),
KEY `officeDistrictId` (`officeDistrictId`),
KEY `officeAmphurId` (`officeAmphurId`),
KEY `officeProvinceId` (`officeProvinceId`),
KEY `fatherDistrictId` (`fatherDistrictId`),
KEY `fatherAmphurId` (`fatherAmphurId`),
KEY `fatherProvinceId` (`fatherProvinceId`),
KEY `motherDistrictId` (`motherDistrictId`),
KEY `motherAmphurId` (`motherAmphurId`),
KEY `motherProvinceId` (`motherProvinceId`),
KEY `parentDistrictId` (`parentDistrictId`),
KEY `parentAmphurId` (`parentAmphurId`),
KEY `parentProvinceId` (`parentProvinceId`),
KEY `contactDistrictId` (`contactDistrictId`),
KEY `contactAmphurId` (`contactAmphurId`),
KEY `contactProvinceId` (`contactProvinceId`),
KEY `currentDistrictId` (`currentDistrictId`),
KEY `currentAmphurId` (`currentAmphurId`),
KEY `currentProvinceId` (`currentProvinceId`),
KEY `graduateDistrictId` (`graduateDistrictId`),
KEY `graduateAmphurId` (`graduateAmphurId`),
KEY `graduateProvinceId` (`graduateProvinceId`),
KEY `maritalStatusId` (`maritalStatusId`),
KEY `recruitmentTypeId` (`recruitmentTypeId`),
KEY `entryDegree` (`entryDegree`),
KEY `healthPrivId` (`healthPrivId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `AlumniMain`
--
CREATE TABLE `AlumniMain` (
`alumniId` int(11) NOT NULL auto_increment,
`studentId` int(12) NOT NULL default '0',
`studentCode` varchar(10) default NULL,
`levelId` int(2) default NULL,
`programId` int(12) default NULL,
`prefixId` int(2) NOT NULL default '0',
`studentName` varchar(32) NOT NULL,
`studentSurname` varchar(32) NOT NULL,
`studentNameEng` varchar(32) NOT NULL,
`studentSurnameEng` varchar(32) NOT NULL,
`creditAttempt` float NOT NULL default '0',
`creditSatisfy` float NOT NULL default '0',
`GPA` float NOT NULL default '0',
`admitAcadYear` int(4) NOT NULL default '0',
`admitSemester` int(1) NOT NULL default '0',
`admitDate` date NOT NULL default '0000-00-00',
`finishDate` date NOT NULL default '0000-00-00',
`studentPassword` varchar(16) NOT NULL,
`studentEmail` varchar(32) NOT NULL,
`studentYear` int(1) NOT NULL default '0',
`studentStatus` int(2) NOT NULL default '0',
`officerId1` int(12) default NULL,
`officerId2` int(12) default NULL,
`financeStatus` int(2) NOT NULL default '0',
`updateUserId` varchar(20) NOT NULL,
`updateDateTime` datetime NOT NULL default '0000-00-00 00:00:00',
`citizenId` varchar(13) default NULL,
`graduateYear` int(4) NOT NULL default '0',
`genStatus` char(1) NOT NULL default 'N',
`genNo` int(4) NOT NULL default '0',
`entryTypeId` int(2) NOT NULL default '0',
`entryTypeId2` int(2) NOT NULL default '0',
`studentSex` char(1) NOT NULL,
`scholarId` int(11) NOT NULL default '0',
`preAdmitPositionId` int(11) default NULL,
`totalPoint` int(11) NOT NULL default '100',
`honor` char(1) default NULL,
`medal` char(1) default NULL,
`exitExam` char(1) NOT NULL default 'N',
`studentStatusTmp` int(2) NOT NULL default '1',
`importtoalumni` char(2) NOT NULL,
`glantId` varchar(5) NOT NULL,
`glantDate` datetime NOT NULL default '0000-00-00 00:00:00',
`glantreqDate` datetime NOT NULL default '0000-00-00 00:00:00',
`amAnsQn` varchar(1) NOT NULL,
`amProgramAlumni` int(4) default '0',
PRIMARY KEY (`alumniId`),
-- UNIQUE KEY `studentCode` (`studentCode`),
KEY `levelId` (`levelId`),
KEY `programId` (`programId`),
KEY `prefixId` (`prefixId`),
KEY `admitSemester` (`admitSemester`),
KEY `studentYear` (`studentYear`),
KEY `studentStatus` (`studentStatus`),
KEY `officerId1` (`officerId1`),
KEY `officerId2` (`officerId2`),
KEY `financeStatus` (`financeStatus`),
KEY `genNo` (`genNo`),
KEY `entryTypeId` (`entryTypeId`),
KEY `entryTypeId2` (`entryTypeId2`),
KEY `scholarId` (`scholarId`),
KEY `preAdmitPositionId` (`preAdmitPositionId`),
KEY `studentStatusTmp` (`studentStatusTmp`),
KEY `amProgramAlumni` (`amProgramAlumni`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `AlumniQn`
--
CREATE TABLE `AlumniQn` (
`qnId` int(11) NOT NULL,
`alumniId` int(11) NOT NULL,
`checkwork` varchar(2) NOT NULL,
`checkedu` varchar(2) NOT NULL,
`startDateWork` date NOT NULL,
`workPosition` varchar(50) NOT NULL,
`workSalary` int(10) NOT NULL,
`leveleduId` int(3) NOT NULL,
`majoreduId` int(11) NOT NULL,
`educationId` int(11) NOT NULL,
`investiture` varchar(2) NOT NULL,
`updateByPerson` varchar(2) NOT NULL,
`writeDate` date NOT NULL,
PRIMARY KEY (`qnId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_AlumniQn`
--
CREATE TABLE `ea_AlumniQn` (
`aqQnId` int(11) NOT NULL auto_increment,
`aqAlumniId` int(11) NOT NULL,
`aqStateQn` int(1) NOT NULL,
`aqWorkStatus` varchar(1) NOT NULL,
`aqWorkCompanyT` varchar(100) NOT NULL,
`aqWorkCompanyE` varchar(100) NOT NULL,
`aqWorkPosition` varchar(100) NOT NULL,
`aqWorkAddress` varchar(100) NOT NULL,
`aqWorkDtId` int(11) default NULL,
`aqWorkApId` int(11) default NULL,
`aqWorkPrvId` int(11) default NULL,
`aqWorkZipcode` varchar(5) default NULL,
`aqWorkTelephone` varchar(10) default NULL,
`aqWorkSalary` float NOT NULL,
`aqWorkSalarySpe` float NOT NULL,
`aqWorkStartDate` date NOT NULL default '0000-00-00',
`aqDegree` varchar(100) NOT NULL,
`aqLevelId` int(11) default NULL,
`aqMajId` int(11) default NULL,
`aqEduId` int(11) default NULL,
`aqEduStartDate` date NOT NULL default '0000-00-00',
`aqInvestiture` varchar(1) NOT NULL,
`aqUserId` varchar(10) NOT NULL,
`aqUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`aqQnId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_BaseSalary`
--
CREATE TABLE `ea_BaseSalary` (
`bsId` int(4) NOT NULL auto_increment,
`bsEdgId` int(4) NOT NULL,
`bsLevelId` int(4) NOT NULL,
`bsStartUse` date NOT NULL,
`bsFinishUse` date NOT NULL,
`bsAmount` float NOT NULL,
PRIMARY KEY (`bsId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_CurriculumConfig`
--
CREATE TABLE `ea_CurriculumConfig` (
`ccId` int(11) NOT NULL auto_increment,
`ccCode` char(2) NOT NULL,
`ccName` varchar(100) NOT NULL default '',
`ccStatus` char(1) NOT NULL default 'N',
PRIMARY KEY (`ccId`),
UNIQUE KEY `ccCode` (`ccCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_CurriculumDetails`
--
CREATE TABLE `ea_CurriculumDetails` (
`curId` int(4) NOT NULL,
`curImproveY` int(4) NOT NULL,
`curName` varchar(100) NOT NULL,
`curNameE` varchar(100) NOT NULL,
`curAbbr` varchar(100) NOT NULL,
`curAbbrE` varchar(100) NOT NULL,
`curStatus` char(1) NOT NULL,
`curCcId` int(4) NOT NULL,
`ccCode` char(2) NOT NULL,
`ccName` varchar(100) NOT NULL,
`curEdgId` int(4) NOT NULL,
`edgName` varchar(100) NOT NULL,
`edgNameE` varchar(100) NOT NULL,
`edgAbbr` varchar(50) NOT NULL,
`edgAbbrE` varchar(50) NOT NULL,
`curElvId` int(4) NOT NULL,
`levelName` varchar(100) NOT NULL,
`levelNameEng` varchar(100) NOT NULL,
`levelAbbr` varchar(20) NOT NULL,
`levelAbbrEng` varchar(20) NOT NULL,
`curAddType` varchar(1) NOT NULL,
PRIMARY KEY (`curId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_EduDegree`
--
CREATE TABLE `ea_EduDegree` (
`edgId` int(2) NOT NULL auto_increment,
`edgName` varchar(100) NOT NULL default '',
`edgNameE` varchar(100) NOT NULL default '',
`edgAbbr` varchar(50) NOT NULL default '',
`edgAbbrE` varchar(50) NOT NULL default '',
PRIMARY KEY (`edgId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_Generation`
--
CREATE TABLE `ea_Generation` (
`genId` int(4) NOT NULL,
`genAcY` int(4) NOT NULL,
`genNo` int(11) NOT NULL,
`genCurId` int(11) NOT NULL,
`genTmId` int(1) NOT NULL,
PRIMARY KEY (`genId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ea_HisEducation`
--
CREATE TABLE `ea_HisEducation` (
`hedSeqId` int(4) NOT NULL,
`hedAlumniId` int(11) NOT NULL,
`hedDegree` varchar(200) NOT NULL,
`hedLevelId` int(11) NOT NULL,
`hedEduId` int(11) NOT NULL,
`hedMajId` int(11) NOT NULL,
`hedStartDate` date NOT NULL,
`hedFinishDate` date NOT NULL,
`hedUserId` varchar(10) NOT NULL,
`hedUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`hedSeqId`,`hedAlumniId`),
KEY `hedLevelId` (`hedLevelId`),
KEY `hedEduId` (`hedEduId`),
KEY `hedMajId` (`hedMajId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `Education`
--
CREATE TABLE `Education` (
`educationId` int(4) NOT NULL auto_increment,
`educationName` varchar(100) NOT NULL,
`pbriId` int(11) NOT NULL default '0',
PRIMARY KEY (`educationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `HisAward`
--
CREATE TABLE `HisAward` (
`seqId` int(11) NOT NULL,
`alumniId` int(11) NOT NULL,
`awardNameT` varchar(200) NOT NULL,
`awardNameE` varchar(200) NOT NULL,
`awardCompany` varchar(200) NOT NULL,
`typeAwardId` int(4) NOT NULL default '0',
`levelAwardId` int(4) NOT NULL default '0',
`receiveDate` date NOT NULL default '0000-00-00',
`haUserId` varchar(10) NOT NULL,
`haUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`alumniId`,`seqId`),
KEY `typeAwardId` (`typeAwardId`),
KEY `levelAwardId` (`levelAwardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `HisWork`
--
CREATE TABLE `HisWork` (
`seqId` int(11) NOT NULL default '0',
`alumniId` int(11) NOT NULL default '0',
`companyNameT` varchar(200) NOT NULL,
`companyNameE` varchar(200) NOT NULL,
`companyAddr` varchar(200) NOT NULL,
`companyPosition` varchar(200) NOT NULL,
`companyAdmidPosYear` int(4) default NULL,
`companyStartDate` date NOT NULL default '0000-00-00',
`companyEndDate` date NOT NULL default '0000-00-00',
`hwUserId` varchar(10) NOT NULL,
`hwUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`alumniId`,`seqId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ImportUpdate`
--
CREATE TABLE `ImportUpdate` (
`importId` int(11) NOT NULL auto_increment,
`graduateY` int(4) NOT NULL,
`importDate` datetime NOT NULL default '0000-00-00 00:00:00',
`usLogin` varchar(20) NOT NULL,
PRIMARY KEY (`importId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `LevelAward`
--
CREATE TABLE `LevelAward` (
`levelAwardId` int(4) NOT NULL auto_increment,
`levelAwardNameE` varchar(100) NOT NULL,
`levelAwardNameT` varchar(100) NOT NULL,
PRIMARY KEY (`levelAwardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `LevelEdu`
--
CREATE TABLE `LevelEdu` (
`leveleduId` int(4) NOT NULL auto_increment,
`leveleduName` varchar(100) NOT NULL,
`leveleduNameEng` varchar(100) NOT NULL,
`leveleduAbbr` varchar(20) NOT NULL,
`leveleduAbbrEng` varchar(20) NOT NULL,
`pbriId` int(11) NOT NULL default '0',
PRIMARY KEY (`leveleduId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `MajorEdu`
--
CREATE TABLE `MajorEdu` (
`majoreduId` int(4) NOT NULL auto_increment,
`majoreduName` varchar(70) NOT NULL,
`pbriId` int(11) NOT NULL default '0',
PRIMARY KEY (`majoreduId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `MapProgram`
--
CREATE TABLE `MapProgram` (
`mapId` int(11) NOT NULL auto_increment,
`mpGraduateY` int(4) NOT NULL,
`programId_reg` int(11) NOT NULL,
`programId_alumni` int(11) NOT NULL,
PRIMARY KEY (`mapId`),
KEY `programId_reg` (`programId_reg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `ProgramAlumni`
--
CREATE TABLE `ProgramAlumni` (
`programalumniId` int(11) NOT NULL auto_increment,
`programalumniName` varchar(100) NOT NULL,
PRIMARY KEY (`programalumniId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `TimeQn`
--
CREATE TABLE `TimeQn` (
`seqId` int(11) NOT NULL auto_increment,
`graduateY` int(4) NOT NULL,
`startDate` date NOT NULL default '0000-00-00',
`endDate` date NOT NULL default '0000-00-00',
`tqUserId` varchar(10) NOT NULL,
`tqUserUpdate` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`seqId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `TypeAward`
--
CREATE TABLE `TypeAward` (
`typeAwardId` int(4) NOT NULL auto_increment,
`typeAwardNameE` varchar(100) NOT NULL,
`typeAwardNameT` varchar(100) NOT NULL,
PRIMARY KEY (`typeAwardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
|