Page 1 of 1

Database Design for Project Management System?

Posted: Mon May 25, 2009 7:49 pm
by mischievous
Hey guys, im currently working on a project management system and need the ability to have multiple projects assigned to multiple users and groups...

The problem is that I am unsure how to design the database to handle this information efficiently?

Idea 1:
Store this user/group information in the projects table.

Idea 1 Problem:
fields would become comma delimited if multiple users or groups are selected. making searchability a problem.

Idea 2:
storing the information in the users/groups table

Idea 2 Problem:
Again, would run into the problem that a user or group should have the ability to have multiple open projects meaning that this field would again become comma delimited.

Idea 3:
Create a seperate table with one to one ratio.
ie: project_id, user_id, group_id
1 result per row.

Idea 3 Problem:
If multiple users and groups were selected for a certain project this could create up to 10-15 or more rows for one project. This table could get enormous fast.

If you have any ideas or suggestions I would greatly appreciate it!

Thanks,
Anthony

Re: Database Design for Project Management System?

Posted: Tue May 26, 2009 12:29 am
by allspiritseve
mischievous wrote:Idea 3:
Create a seperate table with one to one ratio.
ie: project_id, user_id, group_id
1 result per row.
I would go with this option. Don't worry about the size of the table until it's actually slowing you down.

I'm not sure how the relationship works between projects, users, and groups from the above information. If projects can belong to certain users, but they can also belong to groups (separate from each individual in that group) then I woudl suggest a group_projects table and a user_projects table.

Re: Database Design for Project Management System?

Posted: Tue May 26, 2009 10:50 am
by mischievous
Yeah, thats what I was thinking about... if instead of applying a project to an actual group just apply it to the users inside that group.

But I appreciate your response... that was the option that I figured i would just run with untill it doesnt work... lol