DROP TABLE IF EXISTS forum;
DROP TABLE IF EXISTS message;
DROP TABLE IF EXISTS usertable;
DROP TABLE IF EXISTS adm_moderator;
DROP TABLE IF EXISTS standard_user;
DROP TABLE IF EXISTS interact;

CREATE TABLE forum(
forumname VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR (20) NOT NULL,
defaultSortOrder VARCHAR(15), 
stylesheet VARCHAR(30)
);

DESCRIBE forum;

CREATE TABLE message(
MID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
subject VARCHAR(40),
author VARCHAR(20),
body VARCHAR(200),
dateposted DATE,
in_reply_to INT UNSIGNED,
forumname VARCHAR(20)
);

DESCRIBE message;

CREATE TABLE usertable(
username VARCHAR(20) NOT NULL PRIMARY KEY,
password VARCHAR(20) NOT NULL,
role VARCHAR(15)
);

DESCRIBE usertable;

CREATE TABLE adm_moderator(
username VARCHAR(20) NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
email VARCHAR(30) NOT NULL
);

DESCRIBE adm_moderator;

CREATE TABLE standard_user(
username VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
email VARCHAR(30)
);

DESCRIBE standard_user;
 
CREATE TABLE interact(
username VARCHAR(20) NOT NULL,
forumname VARCHAR(20) NOT NULL,
PRIMARY KEY (username, forumname)
);

DESCRIBE interact;