I am trying to figure out the best way to put certain data into a database. Here is the situation:
There can be N number of projects at any time.
Each project has 5 phases.
For each of those 5 phases, M number of people are assigned to it.
Within each of those assignments, the time spent by the people is recorded.
etc. As you can see, it can get very long if there are many members working on each phase of a project. Also, the project number is repeated over and over again. Would a better solution be to create a table for the allocation for each project, instead of having one big table? Or would the need to create a table be even less efficient? I'm looking for a) normalization and b) efficiency. Any advice would be appreciated.
I don't think you need a Phase table, unless they're actually named phases, and even then you may not need it in the database that way, just create a constraint on the TimeSpent table to limit the entry possibilities.
Anyway, on to the meat of the matter. I can think of a couple of different ways for you to do this - but no, I would definitely not create a seperate table for each project. Yes, the TimeSpent table can get long, but databases are designed to handle large amounts of data, that's what they're for.
Depending on the requirements, you have four options for recording time:
1) Record a begin and start datetime for each person in the TimeSpent table, and calculate the total time spent when needed.
2) Record just the time spent for each period, calculating the total time spent when needed.
3) Keep a running total for each person on time spent during each phase.
4) Like #2, except at the end of each phase, you can do a summary on each member to delete unecessary rows.
And again, depending on which option you go with, you may need another table just to record the members that are assigned to each phase of the project (what if a member is assigned, but ends up spending no time on that phase).