File manager - Edit - /home/webapp69.cm.in.th/u69319090029/Project SME/database.sql
Back
-- CMTC Tech Solution - Database Schema & Seed Data (Full-Function Mode) -- Database Name Constraint: smart_dine_cloud_db -- Engine: InnoDB CREATE DATABASE IF NOT EXISTS `smart_dine_cloud_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `sme_os`; -- 1. Products Table CREATE TABLE IF NOT EXISTS `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, `price` DECIMAL(10, 2) NOT NULL, `status` ENUM('available', 'out_of_stock') DEFAULT 'available', INDEX (`status`) ) ENGINE=InnoDB; -- 2. Orders Table CREATE TABLE IF NOT EXISTS `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `table_number` VARCHAR(50) NOT NULL, `status` ENUM('pending', 'preparing', 'served', 'completed', 'cancelled') DEFAULT 'pending', `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (`status`), INDEX (`created_at`) ) ENGINE=InnoDB; -- 3. Order Items Junction Table CREATE TABLE IF NOT EXISTS `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `product_id` INT DEFAULT NULL, `quantity` INT NOT NULL DEFAULT 1, `price` DECIMAL(10, 2) NOT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB; -- Seed Sample Products for CMTC Food Center INSERT INTO `products` (`name`, `price`, `status`) VALUES ('ข้าวกะเพราหมูสับไข่ดาว (สูตรดั้งเดิม)', 50.00, 'available'), ('ข้าวผัดทะเลรวมมิตร', 60.00, 'available'), ('ผัดไทยกุ้งสดห่อไข่', 65.00, 'available'), ('แกงเขียวหวานไก่ (เสิร์ฟพร้อมข้าวสวย)', 55.00, 'available'), ('ต้มยำกุ้งน้ำข้น (ชาม)', 90.00, 'available'), ('ชาไทยเย็นสูตรเข้มข้น CMTC Tech Solution', 30.00, 'available'), ('กาแฟโบราณเย็น', 30.00, 'available'), ('น้ำดื่มขวดแก้วและน้ำแข็ง', 15.00, 'available'), ('บัวลอยสามสีนมสด (ของหวาน)', 40.00, 'out_of_stock'); -- 4. Shops Table (ร้านค้าในโครงการ) CREATE TABLE IF NOT EXISTS `shops` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, `phone` VARCHAR(50), `plan` VARCHAR(50) NOT NULL, `status` ENUM('active', 'inactive') DEFAULT 'active', `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (`status`) ) ENGINE=InnoDB; -- 5. Shop Admins Table (แอดมินร้านค้า) CREATE TABLE IF NOT EXISTS `shop_admins` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `shop_id` INT NOT NULL, `username` VARCHAR(100) NOT NULL UNIQUE COLLATE utf8mb4_unicode_ci, `password` VARCHAR(255) NOT NULL, `name` VARCHAR(255) NOT NULL COLLATE utf8mb4_unicode_ci, `phone` VARCHAR(50), `email` VARCHAR(100), `status` ENUM('active', 'inactive') DEFAULT 'active', FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE, INDEX (`status`) ) ENGINE=InnoDB;
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.55 |
proxy
|
phpinfo
|
Settings