TL;DR – I’m looking for guidance with my database design. I am concerned that my existing design is inefficient and won’t be able to handle large numbers of employees.

This is going to be long, so please bear with me.
I am in the process of creating a JEE application for staffing demand, supply and usage kind of web application.

So far, I have done the following (the easy part)
Core DB models like Employee, department, location etc.

Key points about demand and supply structure:

1. Allocation for each employee can happen between 0.1 to 1.0.
   2. Each new employee automatically gets allocated to a pool project for 1.0
   3. Need to track an employee’s allocation to a granularity of a week’s allocation. May be required to have daily allocation tracking
      in future.
   4. An employee can be allocated partially (between 0.1 to 1.0) or completely (1.0) for a demand.
   5. Demand is further split into weeks and actual allocation needs to be tracked for each employee on each of this weeks. An employee
      allocated to a demand may be allocated fully or partially to some or
      all of the weeks for this demand.

I am planning to build the demand DB presentation as 3 tables.

Demand Master (DM): This will contain entry for each new demand created. Basically number of employees required and the over all duration of the demand.

DemandLineItem (DLI): Is the level 2 of demand data that stores the employee requirements for the demand.

DemandLineItemDetails(DLID): is the level 3 of details, that keeps details of each allocation for an employee at week level.

The relationship I envision for these 3 tables is like this

DemandMaster(DM) <-- 1 to many --> DemandLineItem (DLI) <-- 1 to many --> DemandLineItemDetails (DLID)

So here is a dummy data set:

Table DM:
DemandId <--> StartDate <--> EndDate
Demand1  <--> 10 Feb 2014 <--> 24 Feb 2014

Table DLI:
DemandId <--> DliId <--> EmployeeRoleRequirement
Demand1  <--> Dli1  <--> Foreman
Demand1  <--> Dli2  <--> LineManager
Demand1  <--> Dli3  <--> Manager

Table DLID:

DLIID <--> DLID_ID <--> allocationstart <--> allocationend
Dli1  <--> DLIID1  <--> 10 Feb 2014 <--> 17 feb 2014
Dli1  <--> DLIID2  <--> 18 feb 2014 <--> 24 feb 2014
Dli2  <--> DLIID3  <--> 18 feb 2014 <--> 24 feb 2014
Dli3  <--> DLIID4  <--> 10 Feb 2014 <--> 17 feb 2014

The problem statement:

1. This is an inefficient way of creating an tracking a demand, because of the number of joins involved.
2. With the number of increased demands, the DLI and DLID data will increase and will be difficult to do fast CRUD operations.
3. If I transpose a similar structure to supply structure, the number of weeks of data for a current employee will be huge, especially because supply structure needs to accommodate at least 2 years of data into future as “un-used” OR “available” allocations.
4. The fact that allocations committed to a demand needs also be subtracted from the above “un-used” or “available” pool makes it more complex.

Is there a better way of maintaining data like this (week/day level data for each employee)?
Maybe a better DB model OR different approach?

I am using mysql-workbench and mysql database for the designing.
Planning to use JPA (hibernate provider) as ORM.