1

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.

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
0

Here's a model that I think would work for you problem.

enter image description here

For assignments that take place for all week days, you can create one row in AssignmentDay per WeekDay for that Assignment.

I'll create an example scenario and sample data to explain:

Jim (idUser=1) is working on the American Express project (idProject=1) every day between Jan 1st and Feb 15th, then every Tuesday and Thursday between March 20th and May 12th. He is also working on the ISLCBS Retail Banking project (idProject=2) on Monday, Wednesday, and Friday between March 20th and May 12th

Sample Data: enter image description here

Like Vérace said, you may just want to use an existing solution to save time.

Improve this answer

Your Answer

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

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