Database design

# fotofactory DATABASE
# Jimmy Symoens 2 ICT 1
# 2006-2007

create database radio;
use radio;

# tabel staffs aanmaken
create table staff (
  staff_id int(10) NOT NULL auto_increment,
  login varchar(20) NOT NULL default '',
  pw varchar(20) NOT NULL default '',
  naam varchar(50) NOT NULL,
  job varchar(255),
  email varchar(255) NOT NULL default '',
  photo_url varchar(255),
  primary key(staff_id)
) engine='innodb';

# tabel shows aanmaken
create table shows (
  show_id int(10) NOT NULL auto_increment,
  name varchar(20) NOT NULL default '',
  show_email varchar(255) NOT NULL default '',
  descr mediumtext,
  primary key(show_id)
) engine='innodb';

# tabel episodes aanmaken
create table episodes (
  episode_id int(10) NOT NULL auto_increment,
  show_id int(10) NOT NULL,
  staff_id int(10),
  foreign key(show_id) references shows(show_id),
  foreign key(staff_id) references staff(staff_id),
  primary key(episode_id)
) engine='innodb';

# tabel program aanmaken
create table program (
  day ENUM ('monday','tuesday','wednesday','thursday','friday','saturday','sunday'),
  begin varchar(5),
  end varchar(5),
  episode_id int(10),
  foreign key(episode_id) references episodes(episode_id),
  primary key(day, begin)
) engine='innodb';