wcaniSPD

1CREATE TABLE IF NOT EXISTS users (
2  id INT NOT NULL AUTO_INCREMENT,
3  firstname VARCHAR(40) NOT NULL,
4  lastname VARCHAR(40) NOT NULL,
5  email VARCHAR(40) NOT NULL,
6  password VARCHAR(80) NOT NULL,
7  reset_password_expires DATETIME,
8  reset_password_token VARCHAR(70),
9  registration_date DATETIME NOT NULL DEFAULT NOW(),
10  last_login_date DATETIME NOT NULL DEFAULT NOW(),
11  PRIMARY KEY (id),
12  UNIQUE (email)
13);
14
15CREATE TABLE IF NOT EXISTS courses (
16  course_id INT NOT NULL AUTO_INCREMENT,
17  title VARCHAR(70) NOT NULL,
18  slug VARCHAR(145),
19  description TEXT,
20  prerequisites TEXT,
21  learning TEXT,
22  creation_date DATETIME NOT NULL DEFAULT NOW(),
23  lastest_update_date DATETIME NOT NULL DEFAULT NOW(),
24  image_url VARCHAR(255),
25  subcategory INT NOT NULL,
26  status INT NOT NULL,
27  author INT NOT NULL,
28  language INT,
29  PRIMARY KEY (course_id),
30  FOREIGN KEY (author) REFERENCES users(id),
31  FOREIGN KEY (subcategory) REFERENCES courses_subcategories(id),
32  FOREIGN KEY (status) REFERENCES courses_status(id),
33  FOREIGN KEY (language) REFERENCES languages(id),
34  UNIQUE (slug),
35  UNIQUE (title)
36);
37
38CREATE TABLE IF NOT EXISTS courses_parts (
39  part_id INT NOT NULL AUTO_INCREMENT,
40  course_id INT NOT NULL,
41  title VARCHAR(70) NOT NULL,
42  PRIMARY KEY (part_id, course_id),
43  FOREIGN KEY (course_id) REFERENCES courses(course_id),
44  UNIQUE (course_id, title)
45);
46
47CREATE TABLE IF NOT EXISTS courses_chapters (
48  chapter_id INT NOT NULL AUTO_INCREMENT,
49  course_id INT NOT NULL,
50  part_id INT NOT NULL,
51  slug VARCHAR(145),
52  content TEXT,
53  title VARCHAR(70) NOT NULL,
54  PRIMARY KEY (chapter_id, course_id, part_id),
55  FOREIGN KEY (part_id, course_id) REFERENCES courses_parts(part_id, course_id),
56  UNIQUE (slug),
57  UNIQUE(course_id, title)
58);