I asked an earlier question about modeling [MySQL data modeling help](https://dba.stackexchange.com/questions/2870/mysql-data-modeling-help). The recommendation was the diagram labeled Multiple Tables for Role and Sponsor. ![Multiple Tables for Role and Sponsor](https://i.stack.imgur.com/UuqlM.gif)

Now I'm trying to write the queries and struggling. It's the point where I kept giving up and looking at maybe easier database models, but now I know that won't work and I need some advice.

When I have an event\_id and a user\_id, I need to check whether this leader is allowed to edit this event. They are allowed if

* Are the user\_id and event\_id is in the user\_event\_role table?
* If this event also has a project\_id, is the user in the user\_project\_role table for the project that runs this event?
* Is the user in the user\_organization\_role table for the organizations that sponsor this project (check the project\_sponsor table)?
* Is the user in the user\_organization\_role table for the organizations that sponsor this event (check the event\_sponsor table)?

If this user\_id is found in any of these leader positions for the event, project, or organizations sponsoring the event or project, then it's okay for this user/leader to edit. 

I'm not sure how to write such a complicated query. The following query insists that the user\_id be present in all three user\_role tables when it only has to be present in one. 

```
SELECT count(*)
FROM user AS u
JOIN user_event_role AS uer ON (uer.user_id=u.id)
JOIN user_project_role AS upr ON (upr.user_id=uer.id)
JOIN user_organization_role AS uor ON (uor.user_id=upr.user_id)
JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
JOIN project_sponsor AS ps ON (ps.project_id=upr.project_id)
WHERE
  uer.term_to >= CURRENT_DATE() AND
  upr.term_to >= CURRENT_DATE() AND
  uor.term_to >= CURRENT_DATE() AND
  u.netid='{$netid}'

```

Do I need to do something as complicated as this union:

```
  SELECT count(*)
  FROM user AS u
  JOIN user_event_role AS uer ON (uer.user_id=u.id)
  WHERE
    uer.event_id=$event_id AND
    uer.term_to >= CURRENT_DATE() AND
    uer.role != 'volunteer' AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_project_role AS upr ON (upr.user_id=u.id)
  JOIN event AS e ON (e.project_id=upr.project_id)
  WHERE
    e.id=$event_id AND
    upr.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'                      
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
  WHERE
    e.id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'      
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN project_sponsor AS ps ON (ps.organization_id=uor.organization_id)        
  WHERE
    uer.event_id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'          

```

Is this the best approach? Is there a way to simplify it?