Created
April 3, 2015 05:05
-
-
Save cpgillem/2f1a66f2b318197d9855 to your computer and use it in GitHub Desktop.
PLAN Website SQL Database setup draft.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE participants ( | |
| first_name varchar(100) NOT NULL, | |
| last_name varchar(200) NOT NULL, | |
| email varchar(200) NOT NULL, | |
| nick varchar(50) NOT NULL, # PLAN nick to use as a primary key. For cclub members, this should be their cclub nick | |
| ccawmu_user varchar(50), # If the participant is a cclub member, their ldap name goes here. | |
| terms boolean NOT NULL, | |
| PRIMARY KEY (email) | |
| ); | |
| CREATE TABLE registrants ( | |
| nick varchar(50) NOT NULL, | |
| mac_address char(17), # might want to put some kind of mask in the web form to enforce properly formatted MACs | |
| lol boolean DEFAULT false, | |
| tf2 boolean DEFAULT false, | |
| cs_go boolean DEFAULT false, | |
| halo_4 boolean DEFAULT false, | |
| super_smash boolean DEFAULT false, | |
| mario_kart_64 boolean DEFAULT false, | |
| magic boolean DEFAULT false, | |
| PRIMARY KEY (email), | |
| FOREIGN KEY (email) REFERENCES participants (email) ON DELETE CASCADE | |
| ); | |
| CREATE TABLE steam_users ( | |
| nick varchar(50) NOT NULL, | |
| steam_username varchar(200) NOT NULL, | |
| PRIMARY KEY (email, steam_username), | |
| FOREIGN KEY (email) REFERENCES participants (email) ON DELETE CASCADE | |
| ); | |
| CREATE TABLE sign_ins ( | |
| nick varchar(50) NOT NULL, | |
| time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| bracelet_id int NOT NULL, | |
| payment_method enum( | |
| "Cash", | |
| "PayPal", | |
| "Prize" | |
| ), | |
| PRIMARY KEY (email), | |
| FOREIGN KEY (email) REFERENCES participants (email) | |
| ); | |
| CREATE TABLE tournaments ( | |
| game varchar(100) NOT NULL, | |
| room char(5) NOT NULL, | |
| time datetime NOT NULL, | |
| PRIMARY KEY (game, room, time) | |
| ); | |
| CREATE TABLE teams ( | |
| name varchar(200) NOT NULL, | |
| captain varchar(50) NOT NULL, | |
| tournament varchar(100), | |
| PRIMARY KEY (name), | |
| FOREIGN KEY (tournaments) REFERENCES tournaments (game) ON DELETE SET NULL | |
| ); | |
| CREATE TABLE team_membership ( | |
| nick varchar(50) NOT NULL, | |
| team varchar(200) NOT NULL, | |
| PRIMARY KEY (nick, team) | |
| ); | |
| CREATE TABLE concession_prices ( | |
| item varchar(50) NOT NULL, | |
| price number(2,2) NOT NULL, | |
| PRIMARY KEY (item) | |
| ); | |
| CREATE TABLE concession_sales ( | |
| quantity int NOT NULL, | |
| item varchar(50) NOT NULL, | |
| time timestamp DEFAULT CURRENT_TIMESTAMP, | |
| ccawmu_user varchar(50) NOT NULL, | |
| paid boolean, | |
| PRIMARY KEY (time), | |
| FOREIGN KEY (item) REFERENCES prices (item) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment