Mysql – professional services (consultants) resource allocation system

database-designMySQLPHP

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.

  1. Straight forward allocation where a consultant is assigned to a project from Jan 1 to July 1st.
  2. 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.
  3. 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
enter image description here

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.

Thanks.

Best Answer

Google "open source project management software" - I got 49 million hits. There appear to be interesting options here, here and here.

There is very little that is truly original in software. What I would advise you to do is to download (a few of the) projects which look interesting, then check out their data models - that's the beauty of Open Source - look at the ideas of others and then adapt them to your own particular situation.

Many of the projects appear to be right up your alley, with many time assignment and/or management options which appear to be the core of your question.

Related Question