1 SET FOREIGN_KEY_CHECKS=0;
2 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
3 SET time_zone = "+00:00";
5 CREATE TABLE `category` (
6 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
7 `title` varchar(100) NOT NULL COMMENT 'Category title',
8 `parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category',
10 INDEX `parent` (`parent`)
11 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Categories';
13 CREATE TABLE `contacts` (
14 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
15 `own_contact` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether own contact',
16 `gender` varchar(10) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'Gender',
17 `first_name` varchar(100) NOT NULL COMMENT 'First name',
18 `family_name` varchar(100) NOT NULL COMMENT 'Family name',
19 `street` varchar(255) DEFAULT NULL COMMENT 'Street name',
20 `house_number` smallint(5) unsigned DEFAULT NULL COMMENT 'House number',
21 `city` varchar(100) DEFAULT NULL COMMENT 'City name',
22 `zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
23 `country_code` char(2) DEFAULT NULL COMMENT 'Country code',
24 `phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
25 `cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
26 `fax_number` varchar(100) DEFAULT NULL COMMENT 'Fax number',
27 `email_address` varchar(100) DEFAULT NULL COMMENT 'Email addres',
28 `birthday` date DEFAULT NULL COMMENT 'Birth day',
29 `comment` tinytext NULL DEFAULT NULL COMMENT 'Comment',
30 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
31 `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated entry',
33 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Contacts data';
35 CREATE TABLE `customer` (
36 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
37 `customer_contact_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference on "contact"',
38 `customer_number` varchar(20) NOT NULL COMMENT 'Customer number',
39 `customer_password_hash` varchar(255) DEFAULT NULL COMMENT 'Password hash',
40 `customer_confirm_key` varchar(50) DEFAULT NULL COMMENT 'Email confirmation key',
41 `customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status',
42 `customer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation',
43 `customer_locked` timestamp NULL DEFAULT NULL COMMENT 'Last locked timestamp',
45 UNIQUE (`customer_confirm_key`),
46 UNIQUE (`customer_number`),
47 INDEX (`customer_contact_id`)
48 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Customer data';
50 CREATE TABLE `ordered_items` (
51 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
52 `order_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference orders',
53 `order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status',
54 `product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products',
55 `item_type` VARCHAR(20) NOT NULL COMMENT 'Item type',
56 `amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount',
57 `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted',
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ordered items';
63 CREATE TABLE `orders` (
64 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
65 `customer_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference customer',
66 `access_key` varchar(100) NULL DEFAULT NULL COMMENT 'Access key to receipt',
67 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order created',
69 INDEX (`customer_id`),
71 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer orders';
73 CREATE TABLE `products` (
74 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
75 `category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id',
76 `title` varchar(100) NOT NULL COMMENT 'Title of product',
77 `price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price',
78 `available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available',
81 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Products';
83 ALTER TABLE `category`
84 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
86 ALTER TABLE `contacts`
87 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
89 ALTER TABLE `customer`
90 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
92 ALTER TABLE `ordered_items`
93 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
96 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
98 ALTER TABLE `products`
99 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
101 ALTER TABLE `category`
102 ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
104 ALTER TABLE `customer`
105 ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE;
107 ALTER TABLE `ordered_items`
108 ADD CONSTRAINT `ordered_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;
111 ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`);
113 ALTER TABLE `products`
114 ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
116 SET FOREIGN_KEY_CHECKS=1;