Database Design for Project Management System?
Posted: Mon May 25, 2009 7:49 pm
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
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