User:Gregory Gauthier/OLMEC design notes

The following design notes for OLMEC were posted on my Facebook in April 2011.

Part 1

I'm currently in the planning process for OLMEC (On-Line Match Entry Coordination), a system for running quiz bowl tournaments which intends to allow for distributed score entry. The plan is to use PHP-MySQL. This is my first time using either language, so I'm quite possibly doing something horribly wrong. Unfortunately, the book I'm using as a reference doesn't even mention "Little Bobby Tables" attacks or how to avoid them (it's also from 2004), so I'm stuck having to figure that out from other sources. However, I don't think I'll have to work on the PHP side for a while. Capitalized words below refer to terms that appear in their own tables and have their own ID number. A TEAM has a name, a SCHOOL, and a "head coach", who is a CONTACT. A PLAYER has a first name, a last name, and a TEAM. (Players can only belong to one team; I'm not going to make the design more complicated just so that Illinois coaches can move players between teams during a tournament.) A SCHOOL has a name, a street address, a ZIP CODE, and a faculty/head CONTACT. A ZIP code is the key value for a city and state. A CONTACT has a first name, a last name, a title, a suffix, an address, a ZIP CODE, a home hpone, a cell phone, and an e-mail address. A PHASE (of a tournament) has a name. (Phases are intended to be different stages of a tournament, such as "preliminary rounds," "rebracketed rounds," and "finals" or "tiebreakers") A ROUND has a PHASE to which it belongs, and a PACKET on which it is run, as well as a sequence number for sorting. A PACKET has a name. A GAME has a ROOM in which it is played and a ROUND to which it belongs and a status (either "complete," "in progress," or "scheduled") A ROOM has a name. A PLAYER_SCORING_MECHANIC has a name, an abbreviation, and a point value. The entries in this table correspond to how players may earn (or lose points). For example, a neg might be abbreviated N and have a point value of -5. Or, in tournaments like Solo, each category can be PSM with point value of 1 (and tiebreakers can be their own separate PSM). A TEAM_SCORING_MECHANIC has a name and abbreviation. This covers ways a team can earn points non-individually. For ACF format tournaments, this will have one entry for bonus points. Other formats might have entries for bounceback bonus points, lightning round points, lightning round bounceback points, or worksheet points. Note that for team scoring, there is no set point value assigned to each TSM. A DIVISION has a PHASE to which it belongs and a rank number relative to other divisions in the same phase. (This faciliates automatic ranking of rebracketed divisions.) The database should also have several cross tables to handle many-to-many relationships like players to games played. team_tossups: for each TEAM and GAME in which that team plays, the table contains a number of toss-ups used in that game. player_tossups: for each PLAYER and GAME in which that player plays, the table contains a number of toss-ups heard by that player. team_stats: for each TEAM and GAME and TEAM_SCORING_MECHANIC, the table contains a number of points earned by the given team in the given game with the given TSM. player_stats: for each PLAYER and GAME and PLAYER_SCORING_MECHANIC, the table contains a number of times the player earned (or lost) points with the given PSM. team_divisions: for each TEAM and PHASE in which that team competes, this table contains the DIVISION in which the given team played in the given phase. (Each team may only be in one division in any phase). ===META TABLES=== A USER has a first name, last name, e-mail address, an SHA1 of a salted password, and a rights level, consisting of *Tournament director *Assistant tournament director *Statkeeper *Scorer *Viewer Users have rights to execute mysql queries via PHP subject to access limitations, which I will address later.

Part 2

Good database design, from what I picked up, requires that fields that might add additional values should be in separate tables. So, with that in mind, we reconstruct users: A USER has a first name, a last name, an e-mail address, an SHA1 hash of a salted password, and a PERMISSIONS_LEVEL A PERMISSIONS_LEVEL has a name and flags indicating whether or not a user of that permissions level can perform certain activities. There are six permissions levels in the current design: 0. (not really a permission level) Server administrator. With access to the MySQL database, e can do anything. However, with great power comes great responsibility, as bad queries (like DROPping the ball) can ruin the database or make it inconsistent. 1. Tournament director. There must be exactly one. Can do most database operations. Can add, remove, and modify TEAMs, PLAYERs, SCHOOLs, ZIP CODEs, CONTACTs, PHASEs, ROUNDs, PACKETs, GAMEs, ROOMs, PLAYER_SCORING_MECHANISMs, TEAM_SCORING_MECHANISMs, DIVISIONs, and the statistical and team-division cross tables. Can create USERs (who start with no permissions) and delete other USERs. Can reset other USERs passwords and can change eir own password. Can promote other USERs to any lower rank. Can demote other USERs of lower rank. Can transfer rights to an assistant tournament director, whereupon this user becomes an assistant tournament direction and the USER to whom the rights were transferred becomes the tournament director. 2. Assistant tournament director. Can be more than one. Can do most database operations. Can add, remove, and modify TEAMs, PLAYERs, SCHOOLs, ZIP CODEs, CONTACTs, PHASEs, ROUNDs, PACKETs, GAMEs, ROOMs, DIVISIONs, and the statistical and team-division cross tables. Can create USERs (who start with no permissions) and delete other USERs who are not the tournament director or an assistant tournament director. Can reset passwords of USERs of lower rank. Can promote other USERs to any lower rank. Can demote other USERs of lower rank. 3. Scheduler. Can be more than one, but this is intended for a bracket expert or RED (Really Easy Direction, a program I am considering writing in the future). This user is intended to allow games to automatically be created in the system. Can create GAMEs between TEAMs who exist. Can create PHASEs, ROUNDs, and DIVISIONs. Can assign TEAMs to DIVISIONs. Can grant or revoke permission for a scorer to edit a particular GAME. 4. Scorekeeper. Can be more than one. This role is intended for users who are inputting stats generally (as in a tournament headquarters). They are basically scorers who have permission to edit every game. Scorekeepers can add, modify, and remove PLAYERs. Scorekeepers can modify the statistical cross tables. 5. Scorer. Can be more than one. This role is intended for scorekeepers or moderators who are inputting stats from their particular game room. Must have permission for each game they wish to edit. They can add PLAYERs to teams if the player will be on a team which the scorer has permission to edit. They can modify or remove PLAYERs provided that the player is on a team that the scorer has permission to edit, and all the games in which the player has played the scorer has permission to edit. Can create or modify records in the statistical cross tables, provided that the scorer has permission to modify the game listed in the record. 6. Viewer. Can be more than one. The default class. Has no special permissions to edit anything. Regardless, all users (including ones who are not logged in), can run predetermined queries to generate statistical reports.