WikiStart: database-design-0.1.sql

-- ------------------------------------------------------------
-- Lots of account information.
-- ------------------------------------------------------------

CREATE TABLE accounts (
  user_id INTEGER UNSIGNED NOT NULL,
  username VARCHAR(255) NOT NULL,
  password VARCHAR(40) NOT NULL,
  salt INTEGER(4) NOT NULL,
  type ENUM('client','employee','admin') NOT NULL,
  sex ENUM('m' , 'f') NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  prefix VARCHAR(10) NULL,
  last_name VARCHAR(45) NOT NULL,
  street VARCHAR(50) NULL,
  nr VARCHAR(10) NULL,
  city VARCHAR(20) NULL,
  zipcode VARCHAR(6) NULL,
  email VARCHAR(255) NULL,
  PRIMARY KEY(user_id)
);

-- ------------------------------------------------------------
-- Account related setting. Stores settings as an association between the setting name and user_id (not as a single user_id with a given number of settings). This allows for a variable number of settings, so the database structure won't have to change often.
-- ------------------------------------------------------------

CREATE TABLE account_settings (
  user_id INTEGER UNSIGNED NOT NULL,
  setting_name VARCHAR(20) NOT NULL,
  value VARCHAR(255) NULL,
  PRIMARY KEY(user_id, setting_name)
);

-- ------------------------------------------------------------
-- This table associates certain users (with type ' client' ) with students.
-- ------------------------------------------------------------

CREATE TABLE client_student_junction (
  client_id INTEGER UNSIGNED NOT NULL,
  student_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(client_id, student_id)
);

-- ------------------------------------------------------------
-- This table can be used to store all kinds of settings and preferences, because we should only use a flat file for basic, one-time settings.
-- ------------------------------------------------------------

CREATE TABLE configuration (
  setting_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NULL,
  value VARCHAR(255) NULL,
  PRIMARY KEY(setting_id)
);

-- ------------------------------------------------------------
-- This table associates certain users (with type 'employee') with the groups they belong to.
-- ------------------------------------------------------------

CREATE TABLE employee_group_junction (
  employee_id INTEGER UNSIGNED NOT NULL,
  group_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(employee_id, group_id)
);

-- ------------------------------------------------------------
-- Links employees with the meetings that they can attend.
-- ------------------------------------------------------------

CREATE TABLE employee_meetings (
  employee_id INTEGER UNSIGNED NOT NULL,
  meeting_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(employee_id, meeting_id)
);

-- ------------------------------------------------------------
-- Event information.
-- ------------------------------------------------------------

CREATE TABLE events (
  event_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  friendly_name VARCHAR(45) NOT NULL,
  tag VARCHAR(45) NOT NULL,
  event_status ENUM('planning','employees','clients','closed','done') NOT NULL,
  notify_employees DATETIME NOT NULL,
  notify_clients DATETIME NOT NULL,
  close_date DATETIME NOT NULL,
  PRIMARY KEY(event_id)
);

-- ------------------------------------------------------------
-- Associates events with their relevant groups.
-- ------------------------------------------------------------

CREATE TABLE event_group_junction (
  group_id INTEGER UNSIGNED NOT NULL,
  event_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(group_id, event_id)
);

-- ------------------------------------------------------------
-- Information about groups.
-- ------------------------------------------------------------

CREATE TABLE groups (
  group_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  friendly_name VARCHAR(45) NOT NULL,
  tag VARCHAR(45) NOT NULL,
  priority TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY(group_id)
);

-- ------------------------------------------------------------
-- Events are made up of several meetings. This table stores information about meetings.
-- ------------------------------------------------------------

CREATE TABLE meetings (
  meeting_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  event_id INTEGER UNSIGNED NOT NULL,
  start_time DATETIME NULL,
  slot_duration TIME NULL,
  slots INTEGER UNSIGNED NULL,
  PRIMARY KEY(meeting_id)
);

-- ------------------------------------------------------------
-- Basic information about students. Note that students are not users and can therefore not login.
-- The only information we require at the moment is the studentnumber (the student_id column is there to provide a uniform way to link other tables).
-- ------------------------------------------------------------

CREATE TABLE students (
  student_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  student_tag VARCHAR(20) NOT NULL,
  name VARCHAR(255) NULL,
  PRIMARY KEY(student_id)
);

-- ------------------------------------------------------------
-- Associates students with a certain group.
-- ------------------------------------------------------------

CREATE TABLE student_group_junction (
  group_id INTEGER UNSIGNED NOT NULL,
  student_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(group_id, student_id)
);

-- ------------------------------------------------------------
-- Keeps track of user choices regarding 'who' and 'when'.
-- After the close_date of an event (that is when the schedule is being created), the 'choices' will be replaced by the actual data (this will probably only involve setting the slot number, which can't be chosen by the user anyway).
-- ------------------------------------------------------------

CREATE TABLE student_meetings (
  meeting_id INTEGER UNSIGNED NOT NULL,
  student_id INTEGER UNSIGNED NOT NULL,
  employee_id INTEGER UNSIGNED NOT NULL,
  slot INTEGER UNSIGNED NULL,
  PRIMARY KEY(meeting_id, student_id)
);