1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
CREATE TABLE users (
user_id INT(8) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(30) NOT NULL,
user_password VARCHAR(255) NOT NULL,
user_level ENUM('user', 'moderator', 'administrator') NOT NULL DEFAULT 'user',
user_date_registered DATETIME NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
);
CREATE TABLE categories (
category_id INT(8) NOT NULL AUTO_INCREMENT,
category_name VARCHAR(255) NOT NULL,
category_description VARCHAR(255) NOT NULL,
category_thread_count INT(8) NOT NULL DEFAULT 0,
category_post_count INT(8) NOT NULL DEFAULT 0,
UNIQUE INDEX category_name_unique (category_name),
PRIMARY KEY (category_id)
);
CREATE TABLE threads (
thread_id INT(8) NOT NULL AUTO_INCREMENT,
thread_subject VARCHAR(255) NOT NULL,
thread_date_created DATETIME NOT NULL,
thread_last_post_date DATETIME NOT NULL,
thread_category INT(8) NOT NULL,
thread_author INT(8) NOT NULL,
PRIMARY KEY (thread_id)
);
CREATE TABLE posts (
post_id INT(8) NOT NULL AUTO_INCREMENT,
post_content TEXT NOT NULL,
post_date_created DATETIME NOT NULL,
post_date_edited DATETIME,
post_thread INT(8) NOT NULL,
post_author INT(8) NOT NULL,
PRIMARY KEY (post_id)
);
ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(category_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE posts ADD FOREIGN KEY(post_thread) REFERENCES threads(thread_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
|
