1 CREATE TABLE IF NOT EXISTS `category` (
2 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
3 `title` varchar(255) NOT NULL COMMENT 'Category title',
4 `parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category',
6 INDEX `parent` (`parent`)
7 ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Categories' ;
9 CREATE TABLE IF NOT EXISTS `contacts` (
10 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
11 `own_contact` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether own contact',
12 `gender` varchar(10) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'Gender',
13 `first_name` varchar(100) NOT NULL COMMENT 'First name',
14 `family_name` varchar(100) NOT NULL COMMENT 'Family name',
15 `company_name` varchar(255) DEFAULT NULL COMMENT 'Company name',
16 `street` varchar(255) DEFAULT NULL COMMENT 'Street name',
17 `house_number` smallint(5) unsigned DEFAULT NULL COMMENT 'House number',
18 `city` varchar(100) DEFAULT NULL COMMENT 'City name',
19 `zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
20 `country_code` char(2) DEFAULT NULL COMMENT 'Country code',
21 `phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
22 `cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
23 `fax_number` varchar(100) DEFAULT NULL COMMENT 'Fax number',
24 `email_address` varchar(100) DEFAULT NULL COMMENT 'Email addres',
25 `birthday` date DEFAULT NULL COMMENT 'Birth day',
26 `comment` tinytext NULL DEFAULT NULL COMMENT 'Comment',
27 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
28 `updated` timestamp NULL DEFAULT NULL COMMENT 'Entry updated',
30 ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Contacts data' ;
32 CREATE TABLE IF NOT EXISTS `customer` (
33 `id` bigint(20) unsigned NOT NULL COMMENT 'Primay key',
34 `customer_contact_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference on "contact"',
35 `customer_number` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'Customer number',
36 `customer_password_hash` varchar(255) COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT 'Password hash',
37 `customer_confirm_key` varchar(50) COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT 'Email confirmation key',
38 `customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status',
39 `customer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation',
40 `customer_locked` datetime NULL DEFAULT NULL COMMENT 'Last locked timestamp',
42 UNIQUE (`customer_confirm_key`),
43 UNIQUE (`customer_number`),
44 INDEX (`customer_contact_id`)
45 ) COMMENT='Customer data' ENGINE='InnoDB' COLLATE 'utf8mb4_general_ci';
47 CREATE TABLE IF NOT EXISTS `products` (
48 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
49 `category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id',
50 `title` varchar(255) NOT NULL COMMENT 'Title of product',
51 `price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price',
52 `available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available',
54 INDEX `category` (`category`)
55 ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Products' ;
57 CREATE TABLE IF NOT EXISTS `orders` (
58 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
59 `order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status',
60 `customer_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Table reference customer',
61 `product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products',
62 `amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount',
63 `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted',
65 INDEX `product_id` (`product_id`),
66 INDEX `customer_product` (`customer_id`,`product_id`)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customer orders';
69 ALTER TABLE `category`
70 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
72 ALTER TABLE `contacts`
73 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
75 ALTER TABLE `customer`
76 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
78 ALTER TABLE `products`
79 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
82 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
84 ALTER TABLE `category`
85 ADD FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL;
87 ALTER TABLE `customer`
88 ADD FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE;
90 ALTER TABLE `products`
91 ADD FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL;
94 ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE SET NULL,
95 ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;