DB Schema for Timesheet Hi I want to create a web based resource tracking system which will be used by my manager using php and html. The db i am using is MySql. I have already created tables for employee, project etc. I have to create table to store the weekly resource availability. For eg. an employee A is working in 2 projects this week, and the ratio is like 0.4 and 0.6 (combining both we get 1.00 so he is engaged in project this week completely). Next week he is working on 1 project and his availability for that project is 0.7(so the manager can put him on another project also). I have to store these information for next 20 weeks. I am not getting any logic to implement this idea in DB. The below screenshot is how my db looks like now. The dates are hard coded. 

Also next week i need to delete the previous weeks information and add 21st week's information for an employee. I completely stuck with this. I saw this link database schema for timesheet but it is some what complex which deals with hours also. I just need the information on weekly basis. Can somebody help me to do this schema ?
I think this could help you.

I create this structure:

Employee
- EmpID (PK)
- EmpName
- TKID

Domain
- DomID (PK)
- DomName

Project
- ProjectID (PK)
- ProjectName
- ProjectStart
- ProjectEnd
- DomID (Asuming your projects are for Domains)

ProjectType
- ProTypeID (PK)
- ProTypeName

RTS
- ProjectID (PK)
- EmpID (PK)
- ProTypeID (PK)
- Active (PK)
- DateStart (PK)
- DateEnd


I fill the tables with the information you post on the screenshot (Only the RTS information is from 2015-06-01 to 2015-06-09).

Employees:



Domains:



Projects:



ProjectTypes:



RTS:



Query:

        SELECT
        E.EmpID,
        E.EmpName,
        E.TKID,
        D.DomName,
        P.ProjectName,
        PT.ProTypeName,
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-01' AND '2015-06-05' &amp;&amp; RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[01-05]',
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-08' AND '2015-06-13' &amp;&amp; RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[08-12]'
    FROM Employe as E
    LEFT JOIN RTS on (RTS.EmpID = E.EmpID)
    LEFT JOIN Project as P ON (P.ProjectID = RTS.ProjectID)
    LEFT JOIN Domain as D ON (D.DomID = P.DomID)
    LEFT JOIN ProjectType as PT ON (PT.ProTypeID = RTS.ProTypeID)
    GROUP BY E.EmpID;


Result:



The only problem with this is that you have to put the week date parameters you want to calculate. You can make a Stored Procedure that could read eStartDate and eEndDate variables and calculate the weeks between those dates, and all you have to do is make a Dynamic Query that concatenate every week between the parameters.  

Example:

CALL SP_RTS('2015-06-01','2015-06-31',1);


Between 2015-06-01 and 2015-06-31 exists 4 weeks monday-friday, you'll need to concatenate 4 columns with those dates. I could help you but I dont know if my post if what are you looking for, if it is, let me know.