CREATE TABLE IF NOT EXISTS `orders` (
`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
+ `customer_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference customer',
+ `access_key` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'Access key to receipt',
+ `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order created',
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `access_key` (`access_key`),
+ INDEX `customer_id` (`customer_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Customer orders';
+
+CREATE TABLE IF NOT EXISTS `ordered_items` (
+ `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
+ `order_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Table reference orders',
`order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status',
- `customer_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Table reference customer',
`product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products',
`amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount',
`added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted',
PRIMARY KEY (`id`),
+ INDEX `order_id` (`order_id`),
INDEX `product_id` (`product_id`),
INDEX `customer_product` (`customer_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customer orders';
ALTER TABLE `orders`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
+ALTER TABLE `ordered_items`
+MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
+
ALTER TABLE `category`
ADD FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL;
ALTER TABLE `products`
ADD FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL;
-ALTER TABLE `orders`
-ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE SET NULL,
+ALTER TABLE `ordered_items`
ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;