MySQL employee database design example

In this article, we will share an employee database design example, which can be useful if you are looking for a Master-Detail database schema for educational or explanatory purposes.

The design of the database that we are going to share is the following:

Image 1. Employee database design

As you can see in the design, besides the employee table, the database will have other five tables: department, department_employee, department_manager, payment, and title.

The script to create the database is the following:

CREATE DATABASE IF NOT EXISTS `employees_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `employees_database`; CREATE TABLE `department` (   `id` char(4) NOT NULL,   `name` varchar(40) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `department` (`id`, `name`) VALUES ('dep9', 'Customer Service'), ('dep5', 'Development'), ('dep2', 'Finance'), ('dep3', 'Human Resources'), ('dep1', 'Marketing'), ('dep4', 'Production'), ('dep6', 'Quality Management'), ('dep8', 'Research'), ('dep7', 'Sales'); CREATE TABLE `department_employee` (   `emp_id` int(11) NOT NULL,   `dept_id` char(4) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `department_employee` (`emp_id`, `dept_id`, `from_date`, `to_date`) VALUES (10001, 'dep5', '1996-06-26', '9999-01-01'), (10002, 'dep7', '2006-08-03', '9999-01-01'), (10003, 'dep4', '2005-12-03', '9999-01-01'), (10004, 'dep4', '1996-12-01', '9999-01-01'), (10005, 'dep3', '1999-09-12', '9999-01-01'), (10006, 'dep5', '2000-08-05', '9999-01-01'), (10007, 'dep8', '1999-02-10', '9999-01-01'), (10008, 'dep5', '2008-03-11', '2018-07-31'), (10009, 'dep6', '2005-02-18', '9999-01-01'), (10010, 'dep4', '2006-11-24', '2010-06-26'), (10010, 'dep6', '2010-06-26', '9999-01-01'); CREATE TABLE `department_manager` (   `emp_id` int(11) NOT NULL,   `dept_id` char(4) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `department_manager` (`emp_id`, `dept_id`, `from_date`, `to_date`) VALUES (10001, 'dep1', '1995-01-01', '2001-10-01'), (10002, 'dep2', '1995-01-01', '1999-12-17'), (10003, 'dep3', '1995-01-01', '2002-03-21'), (10004, 'dep4', '1995-01-01', '1998-09-09'), (10005, 'dep5', '1995-01-01', '2002-04-25'), (10006, 'dep6', '1995-01-01', '1999-05-06'); CREATE TABLE `employee` (   `id` int(11) NOT NULL,   `birth_date` date NOT NULL,   `first_name` varchar(14) NOT NULL,   `last_name` varchar(16) NOT NULL,   `gender` enum('M','F') NOT NULL,   `hire_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `employee` (`id`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10001, '1963-09-02', 'Dorris', 'Bodhi', 'M', '1996-06-26'), (10002, '1974-06-02', 'Bryanna', 'Kolleen', 'F', '1995-11-21'), (10003, '1969-12-03', 'Rayner', 'Colt', 'M', '1996-08-28'), (10004, '1964-05-01', 'Tanner', 'Clifford', 'M', '1996-12-01'), (10005, '1965-01-21', 'Trent', 'Dolph', 'M', '1999-09-12'), (10006, '1963-04-20', 'Ashlee', 'Cristen', 'F', '1999-06-02'), (10007, '1967-05-23', 'Irene', 'Yazmin', 'F', '1999-02-10'), (10008, '1968-02-19', 'Terence', 'Dalton', 'M', '2004-09-15'), (10009, '1962-04-19', 'Regina', 'Wayland', 'F', '1995-02-18'), (10010, '1973-06-01', 'Shanon', 'Mortimer', 'F', '1999-08-24'); CREATE TABLE `payment` (   `emp_id` int(11) NOT NULL,   `salary` int(11) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `payment` (`emp_id`, `salary`, `from_date`, `to_date`) VALUES (10001, 70117, '1986-06-26', '1987-06-26'), (10001, 72102, '1987-06-26', '1988-06-25'), (10001, 76074, '1988-06-25', '1989-06-25'), (10001, 76596, '1989-06-25', '1990-06-25'), (10001, 76961, '1990-06-25', '1991-06-25'), (10002, 75828, '1996-08-03', '1997-08-03'), (10002, 75909, '1997-08-03', '1998-08-03'), (10002, 77534, '1998-08-03', '1999-08-03'), (10002, 79366, '1999-08-03', '2000-08-02'), (10002, 81963, '2000-08-02', '2001-08-02'), (10002, 82527, '2001-08-02', '9999-01-01'), (10003, 50006, '1995-12-03', '1996-12-02'), (10003, 53616, '1996-12-02', '1997-12-02'), (10003, 53466, '1997-12-02', '1998-12-02'), (10003, 53636, '1998-12-02', '1999-12-02'), (10003, 53478, '1999-12-02', '2000-12-01'), (10003, 53699, '2000-12-01', '2001-12-01'), (10003, 53311, '2001-12-01', '9999-01-01'); CREATE TABLE `title` (   `emp_id` int(11) NOT NULL,   `title` varchar(50) NOT NULL,   `from_date` date NOT NULL,   `to_date` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `title` (`emp_id`, `title`, `from_date`, `to_date`) VALUES (10001, 'Senior Engineer', '1996-06-26', '9999-01-01'), (10002, 'Staff', '2006-08-03', '9999-01-01'), (10003, 'Senior Engineer', '2005-12-03', '9999-01-01'), (10004, 'Engineer', '1996-12-01', '2005-12-01'), (10004, 'Senior Engineer', '2005-12-01', '9999-01-01'), (10005, 'Senior Staff', '2006-09-12', '9999-01-01'), (10005, 'Staff', '1999-09-12', '2006-09-12'), (10006, 'Senior Engineer', '2000-08-05', '9999-01-01'), (10007, 'Senior Staff', '2006-02-11', '9999-01-01'), (10007, 'Staff', '1999-02-10', '2006-02-11'), (10008, 'Assistant Engineer', '2008-03-11', '2010-07-31'), (10009, 'Assistant Engineer', '1995-02-18', '2000-02-18'), (10009, 'Engineer', '2000-02-18', '2005-02-18'), (10009, 'Senior Engineer', '2005-02-18', '9999-01-01'), (10010, 'Engineer', '2006-11-24', '9999-01-01'); ALTER TABLE `department`   ADD PRIMARY KEY (`id`),   ADD UNIQUE KEY `dept_name` (`name`); ALTER TABLE `department_employee`   ADD PRIMARY KEY (`emp_id`,`dept_id`),   ADD KEY `dept_id` (`dept_id`); ALTER TABLE `department_manager`   ADD PRIMARY KEY (`emp_id`,`dept_id`),   ADD KEY `dept_id` (`dept_id`); ALTER TABLE `employee`   ADD PRIMARY KEY (`id`); ALTER TABLE `payment`   ADD PRIMARY KEY (`emp_id`,`from_date`); ALTER TABLE `title`   ADD PRIMARY KEY (`emp_id`,`title`,`from_date`); ALTER TABLE `department_employee`   ADD CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE,   ADD CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE CASCADE; ALTER TABLE `department_manager`   ADD CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE,   ADD CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE CASCADE; ALTER TABLE `payment`   ADD CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE; ALTER TABLE `title`   ADD CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE;