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 creating a database for a Leave management system. But I don't understand how to arrange table and where.

My goal is:

  • There are four types of leave allowed for an employee (Leave_Types were fixed only four).

  • The types of leave are: Earned Leave, Casual Leave, Medical Leave, Maternity Leave.

  • Earned leave days is different for every employee, other leave days are the same for all. Like Casual = 12, Medical = 200, Maternity = 90.

I have created a database but it is not what I am trying to do:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 12, 2013 at 01:24 AM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `latrix`
--

-- --------------------------------------------------------

--
-- Table structure for table `designation`
--

CREATE TABLE IF NOT EXISTS `designation` (
  `desig_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`desig_id`),
  UNIQUE KEY `desig_id_UNIQUE` (`desig_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `designation`
--

INSERT INTO `designation` (`desig_id`, `name`) VALUES
(1, 'Manager'),
(2, 'Office Assistance');

-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

CREATE TABLE IF NOT EXISTS `employees` (
  `emp_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title_id` int(11) unsigned NOT NULL,
  `name` varchar(60) NOT NULL DEFAULT 'unknown',
  `fileno` varchar(20) NOT NULL DEFAULT 'TBD',
  `max_leave` int(11) NOT NULL,
  `mobile_phone` varchar(15) NOT NULL,
  `leave_left` int(10) unsigned NOT NULL DEFAULT '0',
  `designation_desig_id` int(11) NOT NULL,
  PRIMARY KEY (`emp_id`),
  KEY `idx_title` (`title_id`),
  KEY `idx_payroll` (`fileno`),
  KEY `idx_name` (`name`),
  KEY `fk_employees_designation1` (`designation_desig_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`emp_id`, `title_id`, `name`, `fileno`, `max_leave`, `mobile_phone`, `leave_left`, `designation_desig_id`) VALUES
(1, 1, 'Admin', '0001', 0, 'no phone', 0, 0),
(2, 1, 'Krishno', '1', 0, '', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave`
--

CREATE TABLE IF NOT EXISTS `emp_leave` (
  `emp_leave_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_id` int(11) unsigned NOT NULL,
  `start_date` date NOT NULL DEFAULT '0000-00-00',
  `end_date` date NOT NULL DEFAULT '0000-00-00',
  `workdays` decimal(4,1) unsigned NOT NULL DEFAULT '0.0',
  `type_id` int(11) unsigned NOT NULL,
  `is_half_day` tinyint(1) NOT NULL DEFAULT '0',
  `is_am` tinyint(1) NOT NULL DEFAULT '0',
  `note` text NOT NULL,
  `submit_date` date NOT NULL,
  `approved` tinyint(1) DEFAULT '0',
  `approval_date` date NOT NULL,
  `approval_emp_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`emp_leave_id`),
  KEY `idx_type` (`type_id`),
  KEY `idx_start` (`start_date`),
  KEY `fk_emp_leave_emp` (`emp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `emp_leave`
--

INSERT INTO `emp_leave` (`emp_leave_id`, `emp_id`, `start_date`, `end_date`, `workdays`, `type_id`, `is_half_day`, `is_am`, `note`, `submit_date`, `approved`, `approval_date`, `approval_emp_id`) VALUES
(1, 2, '2013-01-16', '2013-01-18', '0.0', 1, 0, 0, 'bihu', '2013-01-12', 1, '2013-01-12', 1),
(2, 2, '2013-01-22', '2013-01-25', '0.0', 1, 0, 0, 'bihu', '2013-01-12', 1, '2013-01-12', 1);

-- --------------------------------------------------------

--
-- Table structure for table `leave_types`
--

CREATE TABLE IF NOT EXISTS `leave_types` (
  `leave_type_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `description` varchar(200) NOT NULL,
  `isPaid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `isAWOL` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `isAnnual` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `needsNote` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`leave_type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `leave_types`
--

INSERT INTO `leave_types` (`leave_type_id`, `name`, `description`, `isPaid`, `isAWOL`, `isAnnual`, `needsNote`) VALUES
(1, 'CL', '', 1, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `titles`
--

CREATE TABLE IF NOT EXISTS `titles` (
  `title_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`title_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `titles`
--

INSERT INTO `titles` (`title_id`, `name`) VALUES
(1, 'Mr.'),
(2, 'Mrs.'),
(3, 'Ms.'),
(4, 'Dr.');

-- --------------------------------------------------------

--
-- Table structure for table `yesno`
--

CREATE TABLE IF NOT EXISTS `yesno` (
  `idyesno` int(11) NOT NULL,
  `option` varchar(5) NOT NULL,
  PRIMARY KEY (`idyesno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `yesno`
--

INSERT INTO `yesno` (`idyesno`, `option`) VALUES
(1, 'Yes'),
(2, 'No');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `fk_employees_designation1` FOREIGN KEY (`designation_desig_id`) REFERENCES `designation` (`desig_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `employees_ibfk_4` FOREIGN KEY (`title_id`) REFERENCES `titles` (`title_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `emp_leave`
--
ALTER TABLE `emp_leave`
  ADD CONSTRAINT `emp_leave_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `leave_types` (`leave_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `emp_leave_ibfk_2` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`emp_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 */;
share|improve this question
2  
Hello Scriptmafia, and welcome do dba.SE. Could you explain exactly what parts of those requirements you're not managing to implement, or exactly how the database you've designed "doesn't work"? There's a lot of tables there, it would help if you described the problem you're facing more precisely so people can focus on that. –  Mat Jan 12 '13 at 11:20

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.