Database Schema

The following tables show the full database schema for the database used. Note that MySQL does not support referential integrity, so there are no foreign keys shown here. The entity-relation diagram in Figure shows which tables are linked to which others, and in most cases it will be obvious which field names are used.

If a field is marked as auto_increment, that field will automatically have a unique integer assigned to it whenever a tuple is inserted that does not explicitly provide a value for that field, such that the values of that field form a monotonically increasing sequence.

• activity

  Column	Type	Allow NULL	Default	Extra
  activity_id	int(11)	No		auto_increment
  name	char(64)	No
  normal_cost	int(11)	No	0
  user_cost	int(11)	No	0

  • activity_id name, normal_cost, user_cost

• activity_venue

  Column	Type	Allow NULL	Default	Extra
  activity_id	int(11)	No	0
  venue_id	int(11)	No	0

• booking

  Column	Type	Allow NULL	Default	Extra
  booking_id	int(11)	No		auto_increment
  master_id	int(11)	Yes
  verification	char(6)	No
  slot_date	date	No	0000-00-00
  slot_id	int(11)	No	0
  user	char(32)	Yes
  periodic_id	int(11)	Yes
  venue_id	int(11)	No	0
  activity_id	int(11)	No	0
  confirmed	tinyint(4)	No	0
  cost	int(11)	No	0
  book_datetime	datetime	No	0000-00-00 00:00:00

  • booking_id activity_id, book_datetime, confirmed, cost, master_id, periodic_id, slot_date, slot_id, user, venue_id, verification
  • master_id activity_id, book_date, slot_id, slot_id, user

• compound_venue

  Column	Type	Allow NULL	Default	Extra
  compound_id	int(11)	No	0
  part_id	int(11)	No	0

• help

  Column	Type	Allow NULL	Default	Extra
  help_id	int(11)	No		auto_increment
  help_title	varchar(64)	No
  help_text	text	No

  • help_id help_text, help_title

• location

  Column	Type	Allow NULL	Default	Extra
  location_id	int(11)	No		auto_increment
  name	char(64)	No

• periodic

  Column	Type	Allow NULL	Default	Extra
  periodic_id	int(11)	No		auto_increment
  wday	tinyint(4)	No	0
  venue_id	int(11)	No	0
  activity_id	int(11)	No	0
  slot_id	int(11)	No	0
  description	char(255)	No

  • periodic_id activity_id, description, slot_id, venue_id, wday

• schedule

  Column	Type	Allow NULL	Default	Extra
  schedule_id	int(11)	No		auto_increment
  name	char(64)	No

  • schedule_id name

• slot

  Column	Type	Allow NULL	Default	Extra
  slot_id	int(11)	No		auto_increment
  schedule_id	int(11)	No	0
  start_time	time	No	00:00:00
  end_time	time	No	00:00:00

• user

  Column	Type	Allow NULL	Default	Extra
  user	char(32)	No
  email	char(64)	No
  lastname	char(32)	No
  firstname	char(32)	No
  credit	int(11)	No	0
  password	char(13)	No
  pin	char(6)	No
  sports_user	tinyint(4)	No	0
  sports_staff	tinyint(4)	No	0
  sports_admin	tinyint(4)	No	0

  • user credit, email, firstname, lastname, password, pin, sports_admin, sports_staff, sports_user

• venue

  Column	Type	Allow NULL	Default	Extra
  venue_id	int(11)	No		auto_increment
  group_id	int(11)	No	0
  location_id	int(11)	No	0
  name	char(8)	No
  schedule_id	int(11)	No	0

  • venue_id group_id, location_id, name, schedule_id

• venue_group

  Column	Type	Allow NULL	Default	Extra
  group_id	int(11)	No		auto_increment
  name	char(32)	No
  name_sing	char(32)	No

  • group_id name, name_sing