-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.sql
35 lines (31 loc) · 1010 Bytes
/
database.sql
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
-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS coffee_ordering_system;
-- Use the newly created database
USE coffee_ordering_system;
-- Create the coffees table
CREATE TABLE IF NOT EXISTS coffees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) UNIQUE NOT NULL,
description TEXT NOT NULL,
category VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
-- Create the users table
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
role INT DEFAULT 2
);
-- Create the orders table
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
coffee_id INT,
coffee_name VARCHAR(255) NOT NULL,
user_id INT,
quantity INT NOT NULL,
status ENUM('completed', 'failed', 'pending') NOT NULL,
FOREIGN KEY (coffee_id) REFERENCES coffees(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);