Database Design – Scavenger Hunt Game Database Design

Let's start with your original query

   SELECT *  
    FROM reports 
    WHERE report_name IN ( 
        SELECT report_name  
        FROM reports  
        WHERE region IN ( 
            SELECT distinct region  
            FROM macroregions 
            WHERE macroregion IN ( 
                SELECT distinct macroregion 
                FROM users  
                WHERE callsign = '$thisuser' 
            ) 
        )     
    ) 

You can gather keys only (in stages), then join the keys with the reports table. Here is my new proposed query

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        USING (macroregion)
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    USING (region)
) reportnamekeys
INNER JOIN reports
USING (report_name);

If you are not comfortable with the USING clause here is my new propsed query without using the USING clause:

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        ON users.macroregion = regions.macroregion
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    ON regionkeys.region = rpts.region
) reportnamekeys
INNER JOIN reports
ON reportkeys.report_name = reports.report_name;

You will need some indexes to support the subqueries

ALTER TABLE users   ADD INDEX callsign_macroregion_ndx (callsign,macroregion);
ALTER TABLE regions ADD INDEX macroregion_region_ndx (macroregion,region);
ALTER TABLE reports ADD INDEX region_report_name_ndx (region,report_name);
ALTER TABLE reports ADD INDEX report_name_ndx (report_name);

The first 3 indexes are called covering indexes. They are called such because the subqueries calls for only those exact columns. Therefore, no need to read from the table. The data is only fetched from the index.

• http://peter-zaitsev.livejournal.com/6949.html
• http://ronaldbradford.com/blog/tag/covering-index/
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/

You should also study how to refactor queries to gather keys, perform WHERE clauses early, and perform JOINs last. Here is great YouTube Video on how to do this: http://youtu.be/ZVisY-fEoMw (Based on this book : Refactoring SQL Applications)

Give it a Try !!!

If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:

By putting the TeamId in the People table you enforce the fact that a Person can only be on one team. You then have a many:many relationship using a cross join table between People and Roles. This allows a person to have multiple roles. By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role. You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.

The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact. You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId & RoleId WHERE the RoleIds are those that can only have one per team. I haven't tried it though.