Relational Database - design help

[SparkleGirlSparkle]

I have some records that would benefit from being in a relational database; they are currently spread across multiple worksheets in Excel. Now, I made a start on the easier side of the information gathering, but I am getting to the point where I'm not sure how to break stuff done and which relationship and keys are required with which table. The database is to record the number of points earned by the group I run, so that we can see which team has earned the most points over a specific period, and which individual has earned the most. I have written this so many times. Apparently it is really complicated to explain, which is maybe why I can't figure out how to proceed with the rest of the design, so please bear with me.

 

I am going to start with what this is all about. We have a group that works on a points system. The group is split into teams, with specific roles, and the teams can gather points for completely certain tasks, both as individual team members, and as a team. These points get added up over the year, so we can work out which team was the best, and which individual was the best.

 

The first part of my database works fine, with the following tables:

 

- members (id, name, teamsidfk, rolesidfk)

- teams (id, name) [e.g. blue, green, red]

- roles (id, name) [e.g. leader, assistant leader, member]

 

I have happily assigned each team member a team, and their position. So, I can call all leaders, or all members of blue team, without any hassle. Now comes the difficult part of working out how I relate the tasks, their points, and who gets what points for what tasks. The idea is that team members get points for completely individual tasks and special event tasks. These points are added together to make their individual total for the year. Then there are team tasks, which have to be completed as a team. The team total is then made up of the team task totals PLUS individual task totals PLUS individual special events. So, an example might help here:

 

Individual tasks: turn up, wear the correct shoes, bring your equipment - each can be achieved only once per week by each individual, regardless of team, but will be added to weekly team total

Team tasks: be first to complete a task, complete assigned duties - some can only be achieved once per week like the complete duties, whereas the first to complete a task could be achieved by the same team more than once a week, (say there were three tasks and they finished all three tasks first)

Special event: attend first aid, day trip to the zoo - each can be achieved only one over the whole year by each individual, regardless of team, but will be added to weekly team total

 

So, I have made these independent tables:

- tasks_members (id, task, value) - for the individual tasks

- tasks_teams (id, task, value) - for the team tasks

- tasks_events (id, task, value, date) - for the once a year tasks - has date included as it can only occur once

- tasks_date (id, date) - for the weekly date for the year (e.g. 1=1st Jan, 2=8th Jan, 3=15th Jan, etc)

 

Now, I know tasks_events will need a one-to-many relationship between it and the members, as many members can do each special task. tasks_members and members is going to need a many-to-many-to many relationship, as many members can do many tasks on many different dates (ie. every week for the whole year). tasks_teams needs a mixture of one-to-many-to-many and many-to-many-to-many, as some tasks can be done once a week only, and other many times per week. tasks_date needs a one-to-many-to-many as one date will hold many team members doing many tasks. This much I know, but how to break the tables down, and which foreign keys to import to which table in order to add things is where I am getting stuck, because it's that additional relationship I don't know where to add. So, time for another example, I think. Say this is the information I want to add for the first week:

 

Fred, on team blue, on the 1st Jan, turned up and brought his equipment. The blue team completed three tasks first that night.

John, on team blue, on the 1st Jan, turned up with the correct shoes. The blue team completed three tasks first that night.

 

Fred's tasks = 2 points

John's tasks = 2 point

Blue team tasks = 3 points

Blue Team Total (Team + team member points) = 7 points

 

Anne, on team green, on the 1st Jan, turned up, wore the right shoes, and brough her equipment. The green team completed one task first that night. Anne also attended the trip to the zoo.

Bob, on team green, on the 1st Jan, turned up. The green team completed one task first that night.

 

Anne's tasks= 3 points + 1 point (for the zoo) = 4 points

Bob's tasks = 1 point

Green team tasks = 1 point

Green Team Total (Team + team member points) = 6 points

 

So, on the 1st Jan, the results we should get are that Anne was the best individual, and Blue were the best team. If the same information was repeated for the following week (without Anne going to the zoo), then we would get a total of:

 

Fred's tasks = 4 points

John's tasks = 4 points

Blue team tasks = 6points

Blue team total = 14points

 

Anne's tasks = 7 points

Bob's tasks = 2 points

Green team tasks = 2 points

Green team total = 11 points

 

So, Anne is still the best individual, and blue the best team. The idea is to keep that going all year, so we can eventually add up all the points gained, individually, as a team, and from special events to work out the best team and best individual. Once constructed, I hope to also be able to make calls to the database so that I can see all points from team blue, and how they were made up over the year e.g. 55 points from Fred (22 showing up, 13 right shoes, 15 bringing equiptment, 5 special events - zoo, museum, concert, gallery, theatre; 12 points from John (7 showing up, 2 right shoes, 3 bringing equiptment, 0 sepcial events); 33 blue team tasks (22 finishing first, 11 completing duties); 100 blue team total.

 

Please, absolutely feel free to ask any questions! I'm pretty confused myself, so I imagine there will be some. I know the answer lies in getting the right construction and layout between the tables, although I'm not sure if I need another to draw everything together or not. I do feel like I'm really on the verge of solving this, and I just need one or two pointers to achieve the right outcome. I look forward to the questions and suggestions. Thank you in advance for any help.

[Barand]

I'd say you need an extra couple of tables in addition to Person, Team, Role and Member viz.

Table: task
+------------------+-------------+------+-----+---------+-------------------------+
| Field            | Type        | Null | Key | Default | Extra                   |
+------------------+-------------+------+-----+---------+-------------------------+
| idtask           | int(11)     | NO   | PRI | NULL    | auto_increment          |
| task_description | varchar(45) | YES  |     | NULL    |                         |
| task_value       | int(11)     | YES  |     | NULL    |                         |
| special_date     | date        | YES  |     | NULL    |                         |
| task_type        | char(1)     | YES  |     | NULL    | I(nd), S(pecial), T(eam)|
+------------------+-------------+------+-----+---------+-------------------------+

Table: completed_task
+------------+---------+------+-----+---------+-----------------------------------+
| Field      | Type    | Null | Key | Default | Extra                             |
+------------+---------+------+-----+---------+-----------------------------------+
| idcomptask | int(11) | NO   | PRI | NULL    | auto_increment                    |
| idperson   | int(11) | YES  | MUL | NULL    | NULL if team task                 |
| idteam     | int(11) | YES  | MUL | NULL    |                                   |
| idtask     | int(11) | YES  | MUL | NULL    |                                   |
| task_date  | date    | YES  |     | NULL    |                                   |
+------------+---------+------+-----+---------+-----------------------------------+

You wouldn't need the weekly dates table as you can group into weeks using the date fields (unless you use non-standard weeks)

Edited February 16, 2014 by Barand

[SparkleGirlSparkle]

Thanks for the reply. I sort of see where you are going with that. Can I just clarify something? Of the tables I mentioned earlier:

 

- tasks_members (id, task, value) - for the individual tasks

- tasks_teams (id, task, value) - for the team tasks

- tasks_events (id, task, value, date) - for the once a year tasks - has date included as it can only occur once

- tasks_date (id, date) - for the weekly date for the year (e.g. 1=1st Jan, 2=8th Jan, 3=15th Jan, etc)

 

I just need to delete the date one, and then condence all the task tables (be they member, team or special) into one?

 

Meetings are the same day every week, however the special events are only every so often, and never on the same day as a meeting. Would it be better to keep the tables I have already, and then have a completed task table for each one (similar to what you suggested above)? Then I can call from each completed table to work out total points? Especially if different tasks are worth different points?

 

Thanks again

[Barand]

The model I gave should be feasible.

 

From the examples you gave

mysql> SELECT * FROM task;
+--------+--------------------+------------+--------------+-----------+
| idtask | task_description   | task_value | special_date | task_type |
+--------+--------------------+------------+--------------+-----------+
|      1 | Wear correct shoes |          1 | NULL         | I         |
|      2 | Bring equipment    |          1 | NULL         | I         |
|      3 | Turn up            |          1 | NULL         | I         |
|      4 | Team task 1        |          1 | NULL         | T         |
|      5 | Team task 2        |          1 | NULL         | T         |
|      6 | Team task 3        |          1 | NULL         | T         |
|      7 | Trip to zoo        |          1 | 2014-01-02   | S         |
+--------+--------------------+------------+--------------+-----------+

mysql> SELECT * FROM completed_task;
+------------+----------+--------+--------+------------+
| idcomptask | idperson | idteam | idtask | task_date  |
+------------+----------+--------+--------+------------+
|          1 |        1 |      1 |      3 | 2014-01-01 |
|          2 |        1 |      1 |      2 | 2014-01-01 |
|          3 |        2 |      1 |      3 | 2014-01-01 |
|          4 |        2 |      1 |      1 | 2014-01-01 |
|          5 |     NULL |      1 |      4 | 2014-01-01 |
|          6 |     NULL |      1 |      5 | 2014-01-01 |
|          7 |     NULL |      1 |      6 | 2014-01-01 |
|          8 |        3 |      3 |      3 | 2014-01-01 |
|          9 |        3 |      3 |      2 | 2014-01-01 |
|         10 |        3 |      3 |      1 | 2014-01-01 |
|         11 |        3 |      3 |      7 | 2014-01-02 |          <-- special task on different day
|         12 |        4 |      3 |      3 | 2014-01-01 |
|         13 |     NULL |      3 |      5 | 2014-01-01 |
+------------+----------+--------+--------+------------+

Individual points query

SELECT DATE_FORMAT(task_date, '%x %v') as week
, tm.teamname
, p.firstname
, GROUP_CONCAT(t.task_description SEPARATOR ', ') as tasks
, SUM(t.task_value) as TotalPoints
FROM completed_task ct
    INNER JOIN task t USING (idtask)
    INNER JOIN person p USING (idperson)
    INNER JOIN team tm USING (idteam)
GROUP BY week, teamname,firstname
ORDER BY TotalPoints DESC;

+---------+----------+-----------+-----------------------------------------------------------+-------------+
| week    | teamname | firstname | tasks                                                     | TotalPoints |
+---------+----------+-----------+-----------------------------------------------------------+-------------+
| 2014 01 | Green    | Anne      | Trip to zoo, Turn up, Wear correct shoes, Bring equipment |           4 |
| 2014 01 | Blue     | Fred      | Bring equipment, Turn up                                  |           2 |
| 2014 01 | Blue     | John      | Turn up, Wear correct shoes                               |           2 |
| 2014 01 | Green    | Bob       | Turn up                                                   |           1 |
+---------+----------+-----------+-----------------------------------------------------------+-------------+

Team task query

SELECT DATE_FORMAT(task_date, '%x %v') as week
, tm.teamname
, GROUP_CONCAT(t.task_description SEPARATOR ', ') as tasks
, SUM(t.task_value) as TotalPoints
FROM completed_task ct
    INNER JOIN task t USING (idtask)
    INNER JOIN team tm USING (idteam)
WHERE t.task_type = 'T'
GROUP BY week, teamname
ORDER BY TotalPoints DESC;

+---------+----------+---------------------------------------+-------------+
| week    | teamname | tasks                                 | TotalPoints |
+---------+----------+---------------------------------------+-------------+
| 2014 01 | Blue     | Team task 1, Team task 2, Team task 3 |           3 |
| 2014 01 | Green    | Team task 2                           |           1 |
+---------+----------+---------------------------------------+-------------+

Overall team scores query

SELECT DATE_FORMAT(task_date, '%x %v') as week
, tm.teamname
, SUM(t.task_value) as TotalPoints
FROM completed_task ct
    INNER JOIN task t USING (idtask)
    INNER JOIN team tm USING (idteam)
GROUP BY week, teamname
ORDER BY TotalPoints DESC;

+---------+----------+-------------+
| week    | teamname | TotalPoints |
+---------+----------+-------------+
| 2014 01 | Blue     |           7 |
| 2014 01 | Green    |           6 |
+---------+----------+-------------+

[SparkleGirlSparkle]

My goodness! Thank you so much for going that extra mile and explaining how it would all work - that was above and beyond, so I thank you so much for that. That gives me the perfect chance to go through it step by step and pick it apart from the inside out, so I understand it for future use. You have been absolutely wonderful and helpful! Can't wait to try it out in the morning!