PLAN Website SQL Database setup draft.

plan_database.sql

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)
);