electronic leave application database design

Question

I am currently working on a leave application (which is a subset of my e-scheduler project) and I have my database design as follows:

event (event_id, dtstart, dtend... *follows icalendar standard*)

event_leave (event_id*, leave_type_id*, total_days)

_leave_type (leave_type_id, name, max_carry_forward)

_leave_allocation (leave_allocation_id, leave_type_id*, name, user_group_id, total_days, year)

_leave_carry_forward(leave_carry_forward_id, leave_type_id*, user_id, year)

Does anyone here in stackoverflow also working on an e-leave app? mind to share your database design as I am looking for a better design than mine. The problem with my current design only occurs at the beginning of the year when the system is calculating the number of days that can be carried forward.

In total I would have to run 1 + {$number_of users} * 2 queries (the first one to find out the number of allocation rules and the maximum carry forward quota. Then for each user, I need to find out the balance, and then to insert the balance to the database)

Answer 1

Hi Jeffrey

There is always a better design!!

Does your current design work? How many users do you expect (ie does it matter you would have to run x thousand queries).

If the problem of the current design is only at the beginning of the year then perhaps you could live with it!

Cheers

NZS

Answer 2

I'm not following the schema very well (it looks like each leave_type would have a carry forward? There's no user on the event* tables?) but you should be able to dynamically derive the balance at any point in time - including across years.

AAMOF, normalization rules would require you to be able to derive the balance. If you then chose to denormalize for performance is up to you, but the design should support the calculated query. Given that, then calculating the year end carryforward is a single set based query.

Edit: I had to change the schema a bit to accommodate this, and I chose to normalize to make the logic easier - but you can insert denormalization along the way for performance if you need to:

First the tables that are important for this scenario...hopefully my pseudo-syntax will make sense:

User { User_Id (PK) }

// Year may be a tricky business logic issue here...Do you charge the Start or End year
// if the event crosses a year boundary? Or do you just do 2 different events?
// You want year in this table, though, so you can do a FK reference to Leave_Allocation
// Some RDBMS will let you do a FK from a View, though, so you could do that
Event { Event_Id (PK), User_Id, Leave_Type_Id, Year, DtStart, DtEnd, ... 
   // Ensure that events are charged to leave the user has
   FK (User_Id, Leave_Type_Id, Year)->Leave_Allocation(User_Id, Leave_Type_Id, Year)
}

Leave_Type { Leave_Type_Id, Year, Max_Carry_Forward 
   // Max_Carry_Forward would probably change per year
   PK (Leave_Type_Id, Year)
}

// Starting balance for each leave_type and user, per year
// Not sure the name makes the most sense - I think of Allocated as used leave,
// so I'd probably call this Leave_Starting_Balance or something
Leave_Allocation { Leave_Type_Id (FK->Leave_Type.Leave_Type_Id), User_Id (FK->User.User_Id), Year, Total_Days 
   PK (Leave_Type_Id, User_Id, Year)
   // Ensure that leave_type is defined for this year
   FK (Leave_Type_Id, Year)->Leave_Type(Leave_Type_Id, Year)
}

And then, the views (which is where you may want to apply some denormalization):

/* Just sum up the Total_Days for an event to make some other calcs easier */
CREATE VIEW Event_Leave AS
   SELECT
      Event_Id,
      User_Id,
      Leave_Type_Id,
      DATEDIFF(d, DtEnd, DtStart) as Total_Days,
      Year
   FROM Event

/* Subtract sum of allocated leave (Event_Leave.Total_Days) from starting balance (Leave_Allocation) */
/* to get the current unused balance of leave */
CREATE VIEW Leave_Current_Balance AS
   SELECT
      Leave_Allocation.User_Id,
      Leave_Allocation.Leave_Type_Id,
      Leave_Allocation.Year,
      Leave_Allocation.Total_Days - SUM(Event_Leave.Total_Days) as Leave_Balance
   FROM Leave_Allocation
   LEFT OUTER JOIN Event_Leave ON
      Leave_Allocation.User_Id = Event_Leave.User_Id
      AND Leave_Allocation.Leave_Type_Id = Event_Leave.Leave_Type_Id
      AND Leave_Allocation.Year = Event_Leave.Year
   GROUP BY
      Leave_Allocation.User_Id,
      Leave_Allocation.Leave_Type_Id,
      Leave_Allocation.Year,
      Leave_Allocation.Total_Days

Now, our Leave CarryForward query is just the minimum of current balance or maximum carryforward as of midnight on 1/1.

   SELECT
      User_Id,
      Leave_Type_Id,
      Year,
      /* This is T-SQL syntax...your RDBMS may be different, but should be able to do the same thing */
      /* If not, you'd do a UNION ALL to Max_Carry_Forward and select MIN(BalanceOrMax) */
      CASE 
         WHEN Leave_Balance < Max_Carry_Forward 
             THEN Leave_Balance 
         ELSE 
             Max_Carry_Forward 
      END as Leave_Carry_Forward
  FROM Leave_Current_Balance
  JOIN Leave_Type ON
      Leave_Current_Balance.Leave_Type_Id = Leave_Type.Leave_Type_Id
      /* This assumes max_carry_forward is how much you can carry_forward into the next year */
      /* eg,, a max_carry_forward of 300 hours for year 2008, means I can carry_forward up to 300 */
      /* hours into 2009. Otherwise, you'd join on Leave_Current_Balance.Year + 1 if it's how much */
      /* I can carry forward into *this* year. */
      AND Leave_Current_Balance.Year = Leave_Type.Year

So, at the end of the year, you'd insert the CarryForward balances back into LeaveAllocation with the new year.

Answer 3

Further notes on my database design and some use cases.

Table Design

This is the main table (basically based on iCalendar schema) that stores event. The event may be a typical event, or a meeting, public holiday etc.

event (event_id (PK), dtstart, dtend, ... --other icalendar fields--)

If a particular type of event has extra information that I have to keep track, I decorate it with another table. For instance, the table to store e-leave specific information. (total_days is not a computed field as part of the requirements)

event_leave (event_id (PK/FK->event), total_days, leave_type_id (FK->leave_type))

Leave type table stores some information on each leave type. For instance, does the application needs approval/recommendation etc. Besides that, it also stores the maximum carry forward allowed. I assume the maximum carry forward would not be altered frequently.

leave_type (leave_type_id (PK), name, require_support, require_recommend, max_carry_forward)

Users are divided into groups, and each group will be given a number of days available for leave for some of the leave_type. Data stored in this table will be populated annually (a new revision for each year). It only stores the number of leave given for each group, not per user.

leave_allocation (leave_allocation_id, year(PK), leave_type_id (PK/FK->leave_type), total_days, group_id)

Next is the table to store carry forward information. This table will be populated once every year for each user. This table will be populated once a year as calculation on the fly is not easy. The formula of counting leave_carry_forward for the user is:

leave_carry_forward(2009) = min(leave_allocation(2008) + leave_carry_forward(2007) - leave_taken(2008), maximum_carry_forward());

leave_carry_forward (leave_carry_forward_id, user_id, year, total_days)

Some Example Use Cases and Solution

Calculate Balance (WIP)

To calculate balance, I make a query to the view declared as follows

DROP VIEW IF EXISTS leave_remaining_days;
CREATE OR REPLACE VIEW leave_remaining_days AS
    SELECT      year, user_id, leave_type_id, SUM(total_days) as total_days
    FROM        (
      SELECT  allocated.year, usr.uid AS "user_id", allocated.leave_type_id, 
       allocated.total_days
      FROM    users usr
       JOIN app_event._leave_allocation allocated
       ON allocated.group_id = usr.group_id
      UNION
      SELECT  EXTRACT(year FROM event.dtstart) AS "year", event.user_id, 
       leave.leave_type_id, leave.total_days * -1 AS total_days
      FROM    app_event.event event
       LEFT JOIN app_event.event_leave leave
       ON event.event_id = leave.event_id
      UNION
      SELECT  year, user_id, leave_type_id, total_days
      FROM    app_event._leave_carry_forward
  ) KKR
    GROUP BY    year, user_id, leave_type_id;

Populate leave_allocation table at the beginning of year

public function populate_allocation($year) {
    return $this->db->query(sprintf(
        'INSERT INTO %s (%s)' .
            "SELECT '%s' AS year, %s " .
            'FROM   %s ' .
            'WHERE  "year" = %s',
        'event_allocation',
        'year, leave_type_id, total_days ...', //(all the fields in the table)
        empty($year) ? date('Y') : $year,
        'leave_type_id, total_days, ..', //(all fields except year)
        $this->__table,
        empty($year) ? date('Y') - 1 : $year - 1
    ))
    ->count() > 0;  // using the database query builder in Kohana PHP framework
}

Populate leave_carry_forward table at the beginning of year

Find out leave type assigned to the user

I would probably need to rename this view (I am bad in naming stuff...). It is actually a leave_allocation table for a user.

DROP VIEW IF EXISTS user_leave_type;
CREATE OR REPLACE VIEW user_leave_type AS
    SELECT  la.year, usr.uid AS user_id, lt.leave_type_id, lt.max_carry_forward
    FROM    users usr
            JOIN app_event._leave_allocation la
                JOIN app_event._leave_type lt
                ON la.leave_type_id = lt.leave_type_id
            ON usr.group_id = la.group_id

The actual query

INSERT INTO leave_carry_forward (year, user_id, leave_type_id, total_days)
    SELECT      '{$this_year}' AS year, user_id, leave_type_id, MIN(carry_forward) AS total_days
    FROM        (
                    SELECT  year, user_id, leave_type_id, total_days AS carry_forward
                    FROM    leave_remaining_days
                    UNION
                    SELECT  year, user_id, leave_type_id, max_carry_forward AS carry_forward
                    FROM    user_leave_type
                ) KKR
    WHERE       year = {$last_year}
    GROUP BY    year, user_id, leave_type_id;