I am developing a resource allocation system using PHP and mysql. I have consultants and projects and I need to allocate the consultants on to various projects for various periods of time on various dates.
There are three types of allocation options.
- Straight forward allocation where a consultant is assigned to a project from Jan 1 to July 1st.
- Multiple allocations, where a consultant will work on the same projects on random dates. Like Jan 1st to Jan 15th, than March 1st to 31st March and finally June 15th to July 1st.
- Recurring assignments, where there is a start date and the end date and a consultant is working Monday and Tuesday for 6 months on one project and Wednesday, Thursday and Friday on another project for 3 or 6 months whatever.
Here is what I have done so far.
- I have a user table which contains all the consultants.
- I have a project table which contains the projects.
- I have an assignment table that contains the information about which consultant is working on which project. It also contains the starting and end date of the assignment
- Now I have a fourth table - assignment_days. This contains the data of which days/dates the consultant is working on a project.
Here is my problem. I am thinking of saving all the days/dates in this table. This table contains: id, assignmentid, projectid, consultantid, dates/days and completed. So if there is an assignment for 6 months than I will insert 182 days/dates (records) in this table. I am not sure if I am doing it correctly or if it is the most efficient way to do it. If this is not the way to do it. Can you suggest a different solution?
And here is the final output i am trying to achieve
This is the list of all the Mondays dates from today onwards for 6 months (which will go to 12 months eventually). It has a number of days a consultant is working on a particular project and at the bottom is the percent allocation or utilization of a resource in a week.
I sure can use someone elses point of view here.