Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 : Employee data model

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 */;

leave

Please check my data models relationship is correct or not, and give me more suggestion to make it better. thanks

Leave diagram:

share|improve this question

migrated from stackoverflow.com Jul 25 '12 at 12:28

This question came from our site for professional and enthusiast programmers.

1  
What are these 2 fields: employe_id and employe_employe_id ? For example how are you connecting employee to address (or phone), address.id to employe_id or employe_employe_id ? You need to be more explicit, if somebody else will take over the project (or you return to it after a few months/years) it will have a hard time finding the correct relations between tables. –  Radu Maris Jul 24 '12 at 7:21
    
added mysql code too. employe_id is the ID of employee table and employe_employe_id is Self Referencing foreign key of employee table. As per your suggestion i will rename fields which everyone can understand. but this application is for me only –  AryanG Jul 24 '12 at 12:02

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer

Employees can have more than one address, you should have a join table to address for the many to many relationship.

The phone table is designed incorrectly. You do not want to add a column when you get a new phone type.

The whole leave thing makes no sense. Please explain if this is a system to manage leave requests? You should have individual records for leave accumulated and leave taken. Do you need an approvals table? The balance shoudl be figured out at the time of query.

The pormotions table makes no sense at all. You want a table to store the organizational position (And it should be a history table so it should have start and end dates). This table should be updated everytime the postion title changes (they are not always promotions). Same with salary, you want a start and stop date.

You seem to be missing data on who the person reports to. Generally reporting will need to be able to sort through the reporting hierarchy.

YOu also seem to have designed this solely on the basis of the data entry GUI. This is HUGE mistake of epic proportions. With this kind of information, reporting is a far larger problem and you need to consider reporting and how you will need to see the data over time. For someone who works for the comany for ten years, what type of information do you need to call up ablout his history? This is a business critical database and should have been designed by a professional database programmer. There are legal implications to this data, there are security concerns.

How are you planning to protect this information? Pretty much all of this should be unavaliable to most users and admins. It should be encrypted. This is critical privacy data.

Employee records typically include benefits information. They also typically include information concenring awards and performance appraisals and performance warnings.

The use of ID as the PK is a SQL Antipattern (http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_cc_1?s=aps&ie=UTF8&qid=1343231980&sr=1-1-catcorr&keywords=sql+antipatterns). You should use tablenameId.

share|improve this answer
    
I need only one address field, so I made address one to one. I will correct the phone table. I have updated the question with simple diagram of leave, please help me to achieve that. Every year we increase salary of employee, but promotion is little rare case. So when employees salary increase,I I want to show current salary. I dont need Report, and benefits. Thanks I will read that book. –  AryanG Jul 26 '12 at 0:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.