I am new to mysql and I have created a data model for keeping employee record. I am using relationship, Please tell me is this data model is correct as i want. I am also including EER diagram.
This is EER diagram :
I want to achieve this points from my database:
An employee record with their address, contact number, dependents, leave record, salary, promotion, designation and their grade.
Salary and promotion
Employee Salary conditions: Employee's salary increases every year.
Promotion: If employee get promotion then their GRADE and Designation need to change with the salary.
as whole latest salary will be taken as current salary.
Leave
Leave balance: it will contain the leave balance, leave taken and leave remaining.
leave taken: list of the leave taken by the employee.
MySql Code:
CREATE TABLE IF NOT EXISTS `address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`address_one` varchar(50) DEFAULT NULL,
`address_two` varchar(50) DEFAULT NULL,
`state` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `address`
--
INSERT INTO `address` (`id`, `address_one`, `address_two`, `state`) VALUES
(1, 'dfgdf', 'sadasa', 'dfdfdf');
-- --------------------------------------------------------
--
-- Table structure for table `dependent`
--
CREATE TABLE IF NOT EXISTS `dependent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`DOB` date NOT NULL,
`employee_employee_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_dependent_employee1` (`employee_employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `designation`
--
CREATE TABLE IF NOT EXISTS `designation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `designation`
--
INSERT INTO `designation` (`id`, `name`) VALUES
(1, 'Clerk'),
(2, 'Driver');
-- --------------------------------------------------------
--
-- Table structure for table `employee`
--
CREATE TABLE IF NOT EXISTS `employee` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) NOT NULL,
`middle_name` varchar(20) DEFAULT NULL,
`last_name` varchar(20) NOT NULL,
`employee_employee_id` int(11) DEFAULT NULL,
`address_id` int(11) DEFAULT NULL,
`phone_id` int(11) DEFAULT NULL,
PRIMARY KEY (`employee_id`),
KEY `fk_employee_employee` (`employee_employee_id`),
KEY `fk_employee_address1` (`address_id`),
KEY `fk_employee_phone1` (`phone_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `employee`
--
INSERT INTO `employee` (`employee_id`, `first_name`, `middle_name`, `last_name`, `employee_employee_id`, `address_id`, `phone_id`) VALUES
(1, 'John', NULL, 'Doe', NULL, 1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `grade`
--
CREATE TABLE IF NOT EXISTS `grade` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `grade`
--
INSERT INTO `grade` (`id`, `name`) VALUES
(1, 'A');
-- --------------------------------------------------------
--
-- Table structure for table `leave_balance`
--
CREATE TABLE IF NOT EXISTS `leave_balance` (
`employee_employee_id` int(11) NOT NULL,
`leave_type_id` int(11) NOT NULL,
`id` int(11) NOT NULL,
`updated` date NOT NULL,
`leave_balance` int(3) NOT NULL,
`leave_taken` int(7) NOT NULL,
`leave_remaining` int(7) NOT NULL,
PRIMARY KEY (`employee_employee_id`,`leave_type_id`,`id`),
KEY `fk_leave_balance_leave_type1` (`leave_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `leave_taken`
--
CREATE TABLE IF NOT EXISTS `leave_taken` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` date NOT NULL,
`to` date NOT NULL,
`pjd` date NOT NULL,
`reason` date NOT NULL,
`employee_employee_id` int(11) NOT NULL,
`leave_type_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_leave_taken_employee1` (`employee_employee_id`),
KEY `fk_leave_taken_leave_type1` (`leave_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `leave_type`
--
CREATE TABLE IF NOT EXISTS `leave_type` (
`id` int(11) NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- Table structure for table `phone`
--
CREATE TABLE IF NOT EXISTS `phone` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mobile` varchar(15) DEFAULT NULL,
`mobile_one` varchar(15) DEFAULT NULL,
`phone` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `phone`
--
INSERT INTO `phone` (`id`, `mobile`, `mobile_one`, `phone`) VALUES
(1, '8749913539', '983928932', '2266733');
-- --------------------------------------------------------
--
-- Table structure for table `promotion`
--
CREATE TABLE IF NOT EXISTS `promotion` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employee_employee_id` int(11) NOT NULL,
`designation_id` int(11) NOT NULL,
`grade_id` int(11) NOT NULL,
`date` date DEFAULT NULL,
`salary_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_promotion_employee1` (`employee_employee_id`),
KEY `fk_promotion_designation1` (`designation_id`),
KEY `fk_promotion_grade1` (`grade_id`),
KEY `fk_promotion_salary1` (`salary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `promotion`
--
INSERT INTO `promotion` (`id`, `employee_employee_id`, `designation_id`, `grade_id`, `date`, `salary_id`) VALUES
(1, 1, 1, 1, '2012-07-02', 1),
(2, 1, 2, 1, '2012-07-17', 2);
-- --------------------------------------------------------
--
-- Table structure for table `salary`
--
CREATE TABLE IF NOT EXISTS `salary` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`salary` varchar(45) DEFAULT NULL,
`employee_employee_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_salary_employee1` (`employee_employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `salary`
--
INSERT INTO `salary` (`id`, `date`, `salary`, `employee_employee_id`) VALUES
(1, '2012-07-02', '12000', 1),
(2, '2012-07-24', '19000', 1);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `dependent`
--
ALTER TABLE `dependent`
ADD CONSTRAINT `fk_dependent_employee1` FOREIGN KEY (`employee_employee_id`) REFERENCES `employee` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `employee`
--
ALTER TABLE `employee`
ADD CONSTRAINT `fk_employee_employee` FOREIGN KEY (`employee_employee_id`) REFERENCES `employee` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_employee_address1` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_employee_phone1` FOREIGN KEY (`phone_id`) REFERENCES `phone` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `leave_balance`
--
ALTER TABLE `leave_balance`
ADD CONSTRAINT `fk_leave_balance_employee1` FOREIGN KEY (`employee_employee_id`) REFERENCES `employee` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_leave_balance_leave_type1` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `leave_taken`
--
ALTER TABLE `leave_taken`
ADD CONSTRAINT `fk_leave_taken_employee1` FOREIGN KEY (`employee_employee_id`) REFERENCES `employee` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_leave_taken_leave_type1` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `promotion`
--
ALTER TABLE `promotion`
ADD CONSTRAINT `fk_promotion_salary1` FOREIGN KEY (`salary_id`) REFERENCES `salary` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_promotion_designation1` FOREIGN KEY (`designation_id`) REFERENCES `designation` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_promotion_employee1` FOREIGN KEY (`employee_employee_id`) REFERENCES `employee` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_promotion_grade1` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
--
-- Constraints for table `salary`
--
ALTER TABLE `salary`
ADD CONSTRAINT `fk_salary_employee1` FOREIGN KEY (`employee_employee_id`) REFERENCES `employee` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Please check my data models relationship is correct or not, and give me more suggestion to make it better.
thanks
Leave diagram:
Best Answer
Without knowing the business rules it is generally hard to judge if a model is valid or not. However, here are some quick points:
1-Address relationship to employee should be reversed. An employee could have several addresses. Your address modeling may be valid if you are referring here to work address where multiple employees work at the same location. Similarly, there is an issue with the phone to employee relationship.
2-Self reference relationship in Employee must not be mandatory on the many side.
3-Relationship between employee and leave balance is awkward. An employee should have 1 leave balance per year but your columns don't show that clearly.
4-Phone table, should separate area code from actual number for land-line phone numbers.
5-Naming: DOB Date is a bad name. DOB means Date of Birth, so the word date is redundant.
6-Salary is not related to Promotion from a business sense as you have it. Salary is related to grade, level, etc.
7-Some relationships are mandatory in the model where they should not be, I have already pointed out the self-referencing one but you also have the Employee-Dependant relationship. It should not be mandatory. The same is true for leave taken.
From a professional stand point, I think you should not use this mode for a production application as is or even with the remarks I mentioned here because I did not provide a full list. Make sure you consult with a more experienced professional before implementing such a model in production.