project_database.sql

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 COLLATE UTF8_GENERAL_CI;
USE mydb;
CREATE TABLE if NOT EXISTS users(
	user_no INT(20) AUTO_INCREMENT,
	user_category VARCHAR(40) NOT NULL, # 관리자, 판매자, 주최자, 고객
	email VARCHAR(50) UNIQUE KEY,
	user_name VARCHAR(35) NOT NULL,
	phone_number VARCHAR(20) NULL,
	last_modify DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (user_no)
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

CREATE TABLE if NOT EXISTS festival(
	festival_id INT(10) AUTO_INCREMENT,
	user_no INT(20) NOT NULL,
	company_name VARCHAR(50) NOT NULL,
	festival_name VARCHAR(30) NOT NULL,
	period VARCHAR(100) NULL,
	location VARCHAR(100) NOT NULL,
	url VARCHAR(50) NULL,
	last_modify DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (festival_id),
	FOREIGN KEY (user_no) REFERENCES users(user_no) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

CREATE TABLE if NOT EXISTS store(
	store_id INT(10) AUTO_INCREMENT,
	user_no INT(20) NOT NULL,
	festival_id INT(20) NOT NULL,
	store_name VARCHAR(20) NOT NULL,
	store_description VARCHAR(200) NULL,
	contact_number VARCHAR(15) NULL,
	category VARCHAR(20) NOT NULL,
	license_number VARCHAR(100) NOT NULL,
	location_number VARCHAR(20) NOT NULL,
	last_modify DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (store_id),
	FOREIGN KEY (user_no) REFERENCES users(user_no) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (festival_id) REFERENCES festival(festival_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

CREATE TABLE if NOT EXISTS menu(
	menu_id INT(20) AUTO_INCREMENT,
	store_id INT(20) NOT NULL,
	menu_name VARCHAR(20) NOT NULL,
	menu_price VARCHAR(10) NOT NULL,
	last_modify DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (menu_id),
	FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

CREATE TABLE if NOT EXISTS orders(
	order_id INT(20) AUTO_INCREMENT,
	user_no INT(20) NOT NULL,
	store_id INT(10) NOT NULL,
	total_qty INT(10) DEFAULT 1,
	total_price INT(30) NOT NULL,
	payment VARCHAR(20) DEFAULT 'Card',
	order_state VARCHAR(20) DEFAULT '주문중',
	requests VARCHAR(150) NULL,
	last_modify DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (order_id),
	FOREIGN KEY(user_no) REFERENCES users(user_no) ON UPDATE CASCADE,
	FOREIGN KEY(store_id) REFERENCES store(store_id) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

CREATE TABLE if NOT EXISTS order_detail(
	order_detail_id INT(10) AUTO_INCREMENT,
	order_id INT(20) NOT NULL,
	menu_id INT(20) NOT NULL,
	food_price INT(30) NOT NULL,
	food_qty INT(10) DEFAULT 1,
	last_modify DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	created_at DATETIME DEFAULT CURRENT_TIMESTAMP(),
	PRIMARY KEY (order_detail_id),
	FOREIGN KEY (order_id) REFERENCES orders(order_id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (menu_id) REFERENCES menu(menu_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;