Multiple joins in database

This situation is pretty difficult to explain, but I'll do my best.

For school, we have to create a web application (written in PHP) which allows teachers to manage their students' projects and allow these to make peer-evaluation. As there are many students, every projects has multiple projectgroups (and ofcourse you should only peer-evaluate your own group members).

My databasestructure looks like this at the moment:

Table users: contains all user info (user_id is primary)

Table: projects: Contains a project_id开发者_Go百科, a name, a description and a start date.

So far this is pretty easy. But now it gets more difficult.

Table groups: Contains a group_id, a groupname and as a group is specific for a project, it also holds a project_id.

Table groupmembers: A group contains multiple users, but users can be in multiple groups (as they can be active in multiple projects). So this table contains a user_id and a group_id to link these.

At last, admins can decide when users need to do their peer-evaluation and how much time they have for it. So there is a last table evaluations containing an evaluation_id, a start and end date and a project_id (the actual evaluations are stored in a sixth table, which is not relevant for now).

I think this is a good design, but it gets harder when I actually have to use this data. I would like to show a list of evaluations you still have to fill in. The only thing you know is your user_id as this is stored in the session.

So this would have to be done:

1) Run a query on groupmembers to see in which groups the user is.

2) With this result, run a query on groups to see to which projects these groups are related.

3) Now that we know what projects the user is in, the evaluations table should be queried to see if there are ongoing evaluations for this projects.

4) We now know which evaluations are available, but now we also need to check the sixth table to see if the user has already completed this evaluation.

All these steps are dependent on the result of each other, so they should all contain their own error handling. Once the user has chosen the evaluation they wish to fill in (a evaluationID will be send via GET), a lot of new queries will have to be run to check which users this member has in his group and will have to evaluate and another check to see which other groupmembers are already evaluated).

As you see, this is quite complex. With all the errorhandling included, my script will be a real mess. Someone told me a "view" might help in this situation, but I don't really understand why this would help me here.

Is there a good way to do this?

Thank you very much!

you are thinking too procedurally.

all your conditions should be easily entered into one single where clause of a sql statement.

you will end up with a single list of the items to be evaluated. only one list, only one set of error handling.

Not sure if this is exactly right, but try this basic approach. I didn't run this against an actual database so the syntax may need to be tweaked.

select p.project_name
from projects p inner join evaluations e on p.project_id = e.project_id
where p.project_id in (
select project_id 
from projects p inner join groups g on p.project_id = g.project_id
inner join groupmembers gm on gm.group_id = g.group_id
where gm.user_id = $_SESSION['user_id'])

Also, you'll need to make sure that you properly escape your user_id when making it a part of the query, but that is a whole other topic.