5nQKsTSZ

1
2CREATE TABLE IF NOT EXISTS languages (
3  id INT NOT NULL AUTO_INCREMENT,
4  name varchar(49),
5  PRIMARY KEY (id)
6);
7
8CREATE TABLE IF NOT EXISTS users (
9  id INT NOT NULL AUTO_INCREMENT,
10  firstname VARCHAR(55) NOT NULL,
11  lastname VARCHAR(55) NOT NULL,
12  email VARCHAR(45) NOT NULL,
13  password CHAR(76) NOT NULL,
14  reset_password_expires DATETIME,
15  reset_password_token VARCHAR(70),
16  registration_date DATETIME NOT NULL DEFAULT NOW(),
17  last_login_date DATETIME NOT NULL DEFAULT NOW(),
18  PRIMARY KEY (id),
19  UNIQUE (email)
20);
21
22CREATE TABLE IF NOT EXISTS deactivated_users (
23  user_id INT NOT NULL,
24  deactivated_at DATETIME NOT NULL DEFAULT NOW(),
25  PRIMARY KEY (user_id),
26  FOREIGN KEY (user_id) REFERENCES users(id)
27);
28
29CREATE TABLE IF NOT EXISTS roles (
30  id INT NOT NULL AUTO_INCREMENT,
31  name VARCHAR(25) NOT NULL,
32  PRIMARY KEY (id),
33  UNIQUE (name)
34);
35
36CREATE TABLE IF NOT EXISTS users_roles (
37  user_id INT NOT NULL,
38  role_id INT NOT NULL,
39  PRIMARY KEY (user_id, role_id),
40  FOREIGN KEY (user_id) REFERENCES users(id),
41  FOREIGN KEY (role_id) REFERENCES roles(id)
42);
43
44CREATE TABLE IF NOT EXISTS courses_categories (
45  id INT NOT NULL AUTO_INCREMENT,
46  name VARCHAR(80) NOT NULL,
47  PRIMARY KEY (id),
48  UNIQUE (name)
49);
50
51CREATE TABLE IF NOT EXISTS courses_subcategories (
52  id INT NOT NULL AUTO_INCREMENT,
53  name VARCHAR(80) NOT NULL,
54  category_id INT NOT NULL,
55  PRIMARY KEY (id),
56  FOREIGN KEY (category_id) REFERENCES courses_categories(id),
57  UNIQUE (name)
58);
59
60CREATE TABLE IF NOT EXISTS courses_status (
61  id INT NOT NULL AUTO_INCREMENT,
62  status VARCHAR(45) NOT NULL,
63  PRIMARY KEY (id),
64  UNIQUE (status)
65);
66
67CREATE TABLE IF NOT EXISTS courses (
68  course_id INT NOT NULL AUTO_INCREMENT,
69  title VARCHAR(255) NOT NULL,
70  slug VARCHAR(255),
71  description TEXT,
72  prerequisites TEXT,
73  learning TEXT,
74  creation_date DATETIME NOT NULL DEFAULT NOW(),
75  lastest_update_date DATETIME NOT NULL DEFAULT NOW(),
76  image_url VARCHAR(255),
77  subcategory INT NOT NULL,
78  status INT NOT NULL,
79  author INT NOT NULL,
80  language INT,
81  PRIMARY KEY (course_id),
82  FOREIGN KEY (author) REFERENCES users(id),
83  FOREIGN KEY (subcategory) REFERENCES courses_subcategories(id),
84  FOREIGN KEY (status) REFERENCES courses_status(id),
85  FOREIGN KEY (language) REFERENCES languages(id),
86  UNIQUE (slug),
87  UNIQUE (title)
88);
89
90CREATE TABLE IF NOT EXISTS courses_parts (
91  part_id INT NOT NULL AUTO_INCREMENT,
92  course_id INT NOT NULL,
93  title VARCHAR(255) NOT NULL,
94  PRIMARY KEY (part_id, course_id),
95  FOREIGN KEY (course_id) REFERENCES courses(course_id),
96  UNIQUE (course_id, title)
97);
98
99CREATE TABLE IF NOT EXISTS courses_chapters (
100  chapter_id INT NOT NULL AUTO_INCREMENT,
101  course_id INT NOT NULL,
102  part_id INT NOT NULL,
103  slug VARCHAR(255),
104  content TEXT,
105  title VARCHAR(255) NOT NULL,
106  PRIMARY KEY (chapter_id, course_id, part_id),
107  FOREIGN KEY (part_id, course_id) REFERENCES courses_parts(part_id, course_id),
108  UNIQUE (slug),
109  UNIQUE(course_id, title)
110);