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.

Here's my proposed solution:

Project Table (with proj_id)
Person Table (with mem_id)
Phase Table (with phase_id)

And a Time spent Table which goes like this:
proj_id|| phase_id || mem_id || timespent
1 1 1 2
1 1 2 1
1 1 3 3
1 2 1 7
1 2 2 4
1 2 3 10
1 3 1 12
1 4 1 10
1 5 1 6
1 5 2 3
1 5 3 5
2 1 1 8
2 1 2 2

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.

Thanks,
Gemmy

Did the instructor post any notes on the board, or mention any special requirements in class? There appear to be at least two small but significant points missing from your assignment.

-PatP

It's actually just a project I'm helping out my friend with. What other information do you need?

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