4JxurSsG

1CREATE TABLE IF NOT EXISTS Users (
2  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
3  first_name VARCHAR(255) NOT NULL,
4  last_name VARCHAR(255) NOT NULL,
5  email VARCHAR(255) UNIQUE NOT NULL,
6  username VARCHAR(130) UNIQUE NOT NULL,
7  password VARCHAR(255) NOT NULL,
8  phone VARCHAR(50),
9  newsletter ENUM('Yes', 'No') DEFAULT 'Yes',
10  street VARCHAR(45),
11  zip VARCHAR(45),
12  city VARCHAR(45),
13
14  PRIMARY KEY (id)
15);
16
17
18CREATE TABLE IF NOT EXISTS Departments (
19  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
20  parent_id INT UNSIGNED DEFAULT 0,
21  title VARCHAR(255) NOT NULL,
22  summary TINYTEXT DEFAULT NULL,
23  description TEXT DEFAULT NULL,
24
25  PRIMARY KEY (id),
26  FOREIGN KEY (parent_id) REFERENCES Departments (id)
27);
28
29
30CREATE TABLE IF NOT EXISTS Products (
31  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
32  department_id INT UNSIGNED NOT NULL,
33  title VARCHAR(255) NOT NULL,
34  summary TINYTEXT DEFAULT NULL,
35  description TEXT DEFAULT NULL,
36  stock INT DEFAULT 0,
37  price DECIMAL(10,2) DEFAULT 0.00,
38  discount DECIMAL(10,2) DEFAULT 0.00,
39  tax DECIMAL(10,2) DEFAULT 0.00,
40  featured ENUM('Yes','No') DEFAULT 'No',
41  status ENUM('Live','Draft') DEFAULT 'Live',
42
43  PRIMARY KEY (id),
44  FOREIGN KEY (department_id) REFERENCES Departments (id)
45);
46
47
48CREATE TABLE IF NOT EXISTS Departments_Assignments (
49  product_id INT UNSIGNED NOT NULL,
50  department_id INT UNSIGNED NOT NULL,
51
52  PRIMARY KEY (product_id,department_id),
53  FOREIGN KEY (product_id) REFERENCES Products (id),
54  FOREIGN KEY (department_id) REFERENCES Departments (id)
55);
56
57
58CREATE TABLE IF NOT EXISTS Reviews (
59  user_id INT UNSIGNED NOT NULL,
60  product_id INT UNSIGNED NOT NULL,
61  rating INT NOT NULL,
62  comment TEXT DEFAULT NULL,
63
64  PRIMARY KEY (user_id,product_id),
65  FOREIGN KEY (user_id) REFERENCES Users (id),
66  FOREIGN KEY (product_id) REFERENCES Products (id)
67);
68
69
70CREATE TABLE IF NOT EXISTS Keywords (
71  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
72  keyword VARCHAR(255) NOT NULL,
73
74  PRIMARY KEY (id)
75);
76
77
78CREATE TABLE IF NOT EXISTS Keywords_Assignments (
79  product_id INT UNSIGNED NOT NULL,
80  keyword_id INT UNSIGNED NOT NULL,
81
82  PRIMARY KEY (product_id,keyword_id),
83  FOREIGN KEY (product_id) REFERENCES Products (id),
84  FOREIGN KEY (keyword_id) REFERENCES Keywords (id)
85);
86
87
88CREATE TABLE IF NOT EXISTS Orders_Status (
89  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
90  name VARCHAR(45) NOT NULL,
91
92  PRIMARY KEY (id)
93);
94
95
96CREATE TABLE IF NOT EXISTS Orders (
97  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
98  user_id INT UNSIGNED NOT NULL,
99  status_id INT UNSIGNED NOT NULL,
100  tracking_number VARCHAR(255),
101  payment_ref VARCHAR(255),
102  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
103  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
104
105  PRIMARY KEY (id),
106  FOREIGN KEY (user_id) REFERENCES Users (id),
107  FOREIGN KEY (status_id) REFERENCES Orders_Status (id)
108);
109
110
111CREATE TABLE IF NOT EXISTS Orders_Products (
112  order_id INT UNSIGNED NOT NULL,
113  product_id INT UNSIGNED NOT NULL,
114  title VARCHAR(255) NOT NULL,
115  summary TINYTEXT DEFAULT NULL,
116  quantity INT NOT NULL,
117  price DECIMAL(10,2) NOT NULL,
118  tax DECIMAL(10,2) DEFAULT 0.00,
119
120  PRIMARY KEY (order_id,product_id),
121  FOREIGN KEY (order_id) REFERENCES Orders (id),
122  FOREIGN KEY (product_id) REFERENCES Products (id)
123);
124
125
126CREATE TABLE IF NOT EXISTS Settings (
127  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
128  name VARCHAR(255) NOT NULL,
129  description TEXT DEFAULT NULL,
130
131  PRIMARY KEY (id)
132);