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. enter image description here

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 ?

  • The ratio you are trying to calculate (1.00), is based on one day? How many hours spend the employee? Give some more information abut your calcultations please.
    – oNare
    Jun 28, 2015 at 17:41
  • @oNare The time is calculated on weekly basis. for eg. an employee A works on project 1 entire week then it is 1.00, if he works on 2 projects then 0.5 and 0.5 (combining we get 1.00, so he is engaged fully on the week) if he works on project 1 only monday and tuesday rest of the days he is free then the value is 0.4 (2/5) Jun 28, 2015 at 17:47
  • Google and download "open source project management software" - check out their schemas - beg, borrow or steal ideas there?
    – Vérace
    Jun 28, 2015 at 19:04

1 Answer 1


I think this could help you.

I create this structure:

- EmpID (PK)
- EmpName

- DomID (PK)
- DomName

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

- ProTypeID (PK)
- ProTypeName

- 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).


enter image description here


enter image description here


enter image description here


enter image description here


enter image description here


        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-01' AND '2015-06-05' && 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' && 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)


enter image description here

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.


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.

  • I tried this method, this one doesn't allow an employee to work on different projects in a week and display each project information in the result. For eg. A works on project 1 (06/30 to 07/02) full day - so (3/5=0.6 on project 1 for that week) similarly he works on project 2 (06/30 to 07/04) - so 5/5=1.00 on project 2 for the same week. I need to display these 2 details. Also I have to calculate the sum of all employees ratio in each week (ie, 0.5+1.5+0.75 etc), that is to be displayed on the bottom part of the report. Is their any way to do it ? Jul 1, 2015 at 18:58
  • In that query is just calculating the Start day. I'll check it later today to add a BETWEEN sTart - End dates and multiprojects result.
    – oNare
    Jul 1, 2015 at 19:11
  • Hai @oNare I am stuck with this. could you please help me to complete this schema ? Jul 6, 2015 at 14:39
  • Join: chat.stackexchange.com/rooms/25568/onare-s
    – oNare
    Jul 6, 2015 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.