I am developping an applications for project management.
The table project basically has as fields project_id and project_name.
Each project also uses resources which which are stored in the resources table with fields resource_id and resource_name.
The link between the two tables is in the table project_has_resources table which stores project_id and resource_id.
This is just a basic many-to-many relationship (project has many resources, resource can be used by many projects).
When adding a new project, the resources can be chosen with lookupwizard checkboxes. They are stored seperately in the project_has_resources table as found in http://xlinesoft.com/phprunner/docs/select_multiple_values.htm.
The SQL looks like this
SELECT
project.project_id,
project.name,
project_has_resources.resource_id
FROM project
LEFT OUTER JOIN project_has_resources ON project.project_id = project_has_resources.project_id
The problem now is in the list view. If I add a new project with resources, the list view shows the newly added project as many times as the count of the checked resources(which is logical).
I need a solution to this problem. I thought about to possible solutions:
- Build the project and edit the project_settings.php or project_add.php manually before upload.
- Use a view without resources for the list display and point the add and edit links in the list view to the view with the resources
- Don't split the checkbox (not preferable with respect to database normalisation)
I think (hope) that some of you have a more elegant solution.
Thanks in advance
Pieter
