IN view of creating a MIS (Management Information System) one architectural/design issue that's confronting us is managing allocation for set of employees.

Scenario: Employees get allocated to projects and hence get their time (in full or part) for a duration is allocated to a specific task. How can this be represented in DB and Programing model?


Employee E1 is allocated to 2 projects P1 and P2
P1 start date is: 10 Jan 2014 and End date is: 20 Mar 2014
P2 start date is: 15 FEB 2014 and End date is: 1 MAY 2014.

E1 gets allocated to P1 for 0.5 (50%) from 20 JAN to 20 FEB 2014 and then again gets allocated 0.75 (75%) from 1 MAR to 20 MAR 2014.

Now let's consider a scenario where the same employee needs to get allocated 100% to project P2. Based on a DB model or programming model (say Java) I should be able to make the following interpretations:

  • 1> E1 is only available 50% for the time period 15 to 20 FEB 2014.
  • 2> E1 is available 100% for the time period 20 FEB to 1 MAR 2014.
  • 3> E1 is available 25% for the period 1 MAR to 20 MAR 2014.
  • 4> E1 is available 100% for the period 20 MAR to 1 MAY 2014.

I am assuming I will have to maintain the allocation details for each employee in a DB and it's equivalent Java data structures. The problem is we are unable to come up with a structure/ER model that can accommodate such details.

I am assuming similar problems are nothing new and open source/projects will already be there.


1> Can anyone please suggest approach for problem like this?

2> is there any references/links you can provide that will help me a better design.

marked as duplicate by gnat, GlenH7, user40980, Bart van Ingen Schenau, jwenting Nov 11 '14 at 13:06

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.