DB design question???

thawes

I've got a table of projects (projID,title,description) but as part of my display I want to show people a list of services rendered (design,photography,video). So my assumption is to make a services table and then a services_projects table. But it seems that the services_projects joining table would just be a ton of data to do something that doesn't seem like it would require all that. So basically the services_projects table would have (ID,serviceID,projID) and I would insert a new record for each time a service was supposed to be shown for a project.

Is this the best way?

drawmack

Yup that's how you modle many to many relationships in a relational db.

thawes

Thanks for the quick answer. Another question though. When I do my query to with PHP I want to get a checkbox display of all of the services but I want the services selected for that particular project to be already checked. I've already got a query that will show just the services they've selected by doing a multiple table select (join i guess its called). But how can i show all of them and at the same time check the ones off they have selected?

thanks!

drawmack

that would be two queries, one to select the services, then you look through that printing them and while printing them query again to see if that particular service is assigned to that particular project.

ultraslacker

It could also be one query joining projects, services, and services_projects.

thawes

what kind of query? example?

ultraslacker

Originally posted by thawes
I've got a table of projects (projID,title,description) but as part of my display I want to show people a list of services rendered (design,photography,video). So my assumption is to make a services table and then a services_projects table. But it seems that the services_projects joining table would just be a ton of data to do something that doesn't seem like it would require all that. So basically the services_projects table would have (ID,serviceID,projID) and I would insert a new record for each time a service was supposed to be shown for a project.

Is this the best way?

Select p.projID, p.title, p.description, s.name
FROM projects p
INNER JOIN services_projects sp ON (p.projID = sp.projID)
INNER JOIN services s ON (sp.serviceID = s.serviceID)

You may need an OUTER JOIN depending on your data and if you want to return services/projects that do not have a entry in services_projects.