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 `company_name` varchar(255) DEFAULT NULL COMMENT 'Company name',
20 `street` varchar(255) DEFAULT NULL COMMENT 'Street name',
21 `house_number` smallint(5) unsigned DEFAULT NULL COMMENT 'House number',
22 `city` varchar(100) DEFAULT NULL COMMENT 'City name',
23 `zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
24 `country_code` char(2) DEFAULT NULL COMMENT 'Country code',
25 `phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
26 `cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
27 `fax_number` varchar(100) DEFAULT NULL COMMENT 'Fax number',
28 `email_address` varchar(100) DEFAULT NULL COMMENT 'Email addres',
29 `birthday` date DEFAULT NULL COMMENT 'Birth day',
30 `comment` tinytext NULL DEFAULT NULL COMMENT 'Comment',
31 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
32 `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated entry',
34 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Contacts data';
36 CREATE TABLE `customer` (
37 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
38 `customer_contact_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference on "contact"',
39 `customer_number` varchar(20) NOT NULL COMMENT 'Customer number',
40 `customer_password_hash` varchar(255) DEFAULT NULL COMMENT 'Password hash',
41 `customer_confirm_key` varchar(50) DEFAULT NULL COMMENT 'Email confirmation key',
42 `customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status',
43 `customer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation',
44 `customer_locked` timestamp NULL DEFAULT NULL COMMENT 'Last locked timestamp',
46 UNIQUE (`customer_confirm_key`),
47 UNIQUE (`customer_number`),
48 INDEX (`customer_contact_id`)
49 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Customer data';
51 CREATE TABLE `ordered_items` (
52 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
53 `order_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference orders',
54 `order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status',
55 `product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products',
56 `item_type` VARCHAR(20) NOT NULL COMMENT 'Item type',
57 `amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount',
58 `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted',
62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ordered items';
64 CREATE TABLE `orders` (
65 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
66 `customer_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference customer',
67 `access_key` varchar(100) NULL DEFAULT NULL COMMENT 'Access key to receipt',
68 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order created',
70 INDEX (`customer_id`),
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer orders';
74 CREATE TABLE `products` (
75 `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
76 `category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id',
77 `title` varchar(100) NOT NULL COMMENT 'Title of product',
78 `price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price',
79 `available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available',
82 ) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Products';
84 ALTER TABLE `category`
85 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
87 ALTER TABLE `contacts`
88 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
90 ALTER TABLE `customer`
91 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
93 ALTER TABLE `ordered_items`
94 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
97 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
99 ALTER TABLE `products`
100 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
102 ALTER TABLE `category`
103 ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
105 ALTER TABLE `customer`
106 ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE;
108 ALTER TABLE `ordered_items`
109 ADD CONSTRAINT `ordered_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;
112 ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`);
114 ALTER TABLE `products`
115 ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;
117 SET FOREIGN_KEY_CHECKS=1;