This question already has an answer here:
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.