zFPFzxNx

1CREATE DATABASE IF NOT EXISTS unquale
2DEFAULT CHARACTER SET utf8
3DEFAULT COLLATE utf8_general_ci;
4use unquale;
5
6CREATE TABLE IF NOT EXISTS languages (
7  id INT NOT NULL AUTO_INCREMENT,
8  name varchar(15),
9  PRIMARY KEY (id)
10);
11
12CREATE TABLE IF NOT EXISTS users (
13  id INT NOT NULL AUTO_INCREMENT,
14  firstname VARCHAR(40) NOT NULL,
15  lastname VARCHAR(40) NOT NULL,
16  email VARCHAR(40) NOT NULL,
17  password VARCHAR(80) NOT NULL,
18  reset_password_expires DATETIME,
19  reset_password_token VARCHAR(70),
20  registration_date DATETIME NOT NULL DEFAULT NOW(),
21  last_login_date DATETIME NOT NULL DEFAULT NOW(),
22  PRIMARY KEY (id),
23  UNIQUE (email)
24);
25
26CREATE TABLE IF NOT EXISTS deactivated_users (
27  user_id INT NOT NULL,
28  deactivated_at DATETIME NOT NULL DEFAULT NOW(),
29  PRIMARY KEY (user_id),
30  FOREIGN KEY (user_id) REFERENCES users(id)
31);
32
33CREATE TABLE IF NOT EXISTS roles (
34  id INT NOT NULL AUTO_INCREMENT,
35  name VARCHAR(30) NOT NULL,
36  PRIMARY KEY (id),
37  UNIQUE (name)
38);
39
40CREATE TABLE IF NOT EXISTS users_roles (
41  user_id INT NOT NULL,
42  role_id INT NOT NULL,
43  PRIMARY KEY (user_id, role_id),
44  FOREIGN KEY (user_id) REFERENCES users(id),
45  FOREIGN KEY (role_id) REFERENCES roles(id)
46);
47
48CREATE TABLE IF NOT EXISTS courses_categories (
49  id INT NOT NULL AUTO_INCREMENT,
50  name VARCHAR(20) NOT NULL,
51  PRIMARY KEY (id),
52  UNIQUE (name)
53);
54
55CREATE TABLE IF NOT EXISTS courses_subcategories (
56  id INT NOT NULL AUTO_INCREMENT,
57  name VARCHAR(20) NOT NULL,
58  category_id INT NOT NULL,
59  PRIMARY KEY (id),
60  FOREIGN KEY (category_id) REFERENCES courses_categories(id),
61  UNIQUE (name)
62);
63
64CREATE TABLE IF NOT EXISTS courses_status (
65  id INT NOT NULL AUTO_INCREMENT,
66  status VARCHAR(10) NOT NULL,
67  PRIMARY KEY (id),
68  UNIQUE (status)
69);
70
71CREATE TABLE IF NOT EXISTS courses (
72  course_id INT NOT NULL AUTO_INCREMENT,
73  title VARCHAR(70) NOT NULL,
74  slug VARCHAR(145),
75  description TEXT,
76  prerequisites TEXT,
77  learning TEXT,
78  creation_date DATETIME NOT NULL DEFAULT NOW(),
79  lastest_update_date DATETIME NOT NULL DEFAULT NOW(),
80  image_url VARCHAR(255),
81  subcategory INT NOT NULL,
82  status INT NOT NULL,
83  author INT NOT NULL,
84  language INT,
85  PRIMARY KEY (course_id),
86  FOREIGN KEY (author) REFERENCES users(id),
87  FOREIGN KEY (subcategory) REFERENCES courses_subcategories(id),
88  FOREIGN KEY (status) REFERENCES courses_status(id),
89  FOREIGN KEY (language) REFERENCES languages(id),
90  UNIQUE (slug),
91  UNIQUE (title)
92);
93
94CREATE TABLE IF NOT EXISTS courses_parts (
95  part_id INT NOT NULL AUTO_INCREMENT,
96  course_id INT NOT NULL,
97  title VARCHAR(70) NOT NULL,
98  PRIMARY KEY (part_id, course_id),
99  FOREIGN KEY (course_id) REFERENCES courses(course_id),
100  UNIQUE (course_id, title)
101);
102
103CREATE TABLE IF NOT EXISTS courses_chapters (
104  chapter_id INT NOT NULL AUTO_INCREMENT,
105  course_id INT NOT NULL,
106  part_id INT NOT NULL,
107  slug VARCHAR(145),
108  content TEXT,
109  title VARCHAR(70) NOT NULL,
110  PRIMARY KEY (chapter_id, course_id, part_id),
111  FOREIGN KEY (part_id, course_id) REFERENCES courses_parts(part_id, course_id),
112  UNIQUE (slug),
113  UNIQUE(course_id, title)
114);
115
116CREATE TABLE IF NOT EXISTS users_enrolled_courses (
117  user_id INT NOT NULL,
118  course_id INT NOT NULL,
119  enrolled_date DATETIME NOT NULL DEFAULT NOW(),
120  exited_date DATETIME,
121  PRIMARY KEY (user_id, course_id),
122  FOREIGN KEY (user_id) REFERENCES users(id),
123  FOREIGN KEY (course_id) REFERENCES courses(course_id)
124);
125
126CREATE TABLE IF NOT EXISTS users_progression_courses (
127  user_id INT NOT NULL,
128  course_id INT NOT NULL,
129  part_id INT NOT NULL,
130  chapter_id INT NOT NULL,
131  PRIMARY KEY (user_id, part_id, course_id, chapter_id),
132  FOREIGN KEY (chapter_id, course_id, part_id) REFERENCES courses_chapters(chapter_id, course_id, part_id),
133  FOREIGN KEY (user_id, course_id) REFERENCES users_enrolled_courses(user_id, course_id)
134);
135
136CREATE TABLE IF NOT EXISTS users_rated_courses (
137  user_id INT NOT NULL,
138  course_id INT NOT NULL,
139  rating INTEGER NOT NULL,
140  rated_date DATETIME NOT NULL DEFAULT NOW(),
141  PRIMARY KEY (user_id, course_id),
142  FOREIGN KEY (user_id, course_id) REFERENCES users_enrolled_courses(user_id, course_id)
143);