DROP TABLE users;

CREATE TABLE users (
  user_id		INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name		VARCHAR(25),
  user_passwd		VARCHAR(25),
  user_fname		VARCHAR(100),
  user_lname		VARCHAR(100),
  user_realname		VARCHAR(100),
  user_email		VARCHAR(100),
  user_aim		VARCHAR(50),
  user_yahoo		VARCHAR(50),
  user_icq		VARCHAR(50),
  user_msn		VARCHAR(50),
  user_privacy		INT UNSIGNED,
  PRIMARY KEY(user_id));

DROP TABLE snctypes;

CREATE TABLE snctypes (
  type_id		INT UNSIGNED NOT NULL AUTO_INCREMENT,
  type_info		VARCHAR(255),
  PRIMARY KEY(type_id));

DROP TABLE showsncds;

CREATE TABLE showsncds (
  snc_id		INT UNSIGNED NOT NULL AUTO_INCREMENT,
  snc_type_id		INT,
  snc_date		DATE,
  snc_title		VARCHAR(255),
  snc_location		VARCHAR(255),
  PRIMARY KEY(snc_id),
  FOREIGN KEY (snc_type_id) REFERENCES snctypes (type_id));

DROP TABLE setlist;

CREATE TABLE setlist (
  sl_id			INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sl_snc_id		INT,
  sl_title		VARCHAR(255),
  sl_order		INT,
  PRIMARY KEY(sl_id),
  FOREIGN KEY (sl_snc_id) REFERENCES showsncds (snc_id));

DROP TABLE reviews;

CREATE TABLE reviews (
  review_id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  review_snc_id		INT,
  review_user_id	INT,
  review_text		TEXT,
  PRIMARY KEY(review_id),
  FOREIGN KEY (review_user_id) REFERENCES users (user_id),
  FOREIGN KEY (review_snc_id) REFERENCES users (snc_id));
