TaXgqdwd

1-- Simple shop database template
2
3SET NAMES utf8;
4SET time_zone = '+00:00';
5SET foreign_key_checks = 0;
6SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
7
8DROP TABLE IF EXISTS `cart`;
9CREATE TABLE `cart` (
10  `id` int(11) NOT NULL AUTO_INCREMENT,
11  `order` int(11) NOT NULL,
12  `product` int(11) NOT NULL,
13  `quantity` int(6) NOT NULL,
14  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
15  `price` int(11) NOT NULL,
16  `regular_price` int(11) NOT NULL,
17  PRIMARY KEY (`id`),
18  KEY `order` (`order`),
19  KEY `product` (`product`),
20  CONSTRAINT `cart_ibfk_2` FOREIGN KEY (`product`) REFERENCES `products` (`id`),
21  CONSTRAINT `cart_ibfk_1` FOREIGN KEY (`order`) REFERENCES `orders` (`id`)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8;
23
24
25DROP TABLE IF EXISTS `categoies`;
26CREATE TABLE `categoies` (
27  `id` int(11) NOT NULL,
28  `name` varchar(45) NOT NULL,
29  PRIMARY KEY (`id`)
30) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31
32
33DROP TABLE IF EXISTS `clients`;
34CREATE TABLE `clients` (
35  `id` int(11) NOT NULL AUTO_INCREMENT,
36  `name` varchar(64) NOT NULL,
37  `email` varchar(64) NOT NULL,
38  PRIMARY KEY (`id`)
39) ENGINE=InnoDB DEFAULT CHARSET=utf8;
40
41
42DROP TABLE IF EXISTS `manufacturers`;
43CREATE TABLE `manufacturers` (
44  `id` int(11) NOT NULL AUTO_INCREMENT,
45  `name` varchar(64) NOT NULL,
46  `description` varchar(120) DEFAULT NULL,
47  PRIMARY KEY (`id`)
48) ENGINE=InnoDB DEFAULT CHARSET=utf8;
49
50
51DROP TABLE IF EXISTS `orders`;
52CREATE TABLE `orders` (
53  `id` int(11) NOT NULL,
54  `client` int(11) NOT NULL,
55  `number` varchar(24) NOT NULL,
56  PRIMARY KEY (`id`),
57  KEY `client` (`client`),
58  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`client`) REFERENCES `clients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
59) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='orders table for stooring orders specific information';
60
61
62DROP TABLE IF EXISTS `prodcat`;
63CREATE TABLE `prodcat` (
64  `id` int(11) NOT NULL AUTO_INCREMENT,
65  `product` int(11) NOT NULL,
66  `category` int(11) NOT NULL,
67  PRIMARY KEY (`id`),
68  KEY `product` (`product`),
69  KEY `category` (`category`),
70  CONSTRAINT `prodcat_ibfk_2` FOREIGN KEY (`category`) REFERENCES `categoies` (`id`),
71  CONSTRAINT `prodcat_ibfk_1` FOREIGN KEY (`product`) REFERENCES `products` (`id`)
72) ENGINE=InnoDB DEFAULT CHARSET=utf8;
73
74
75DROP TABLE IF EXISTS `products`;
76CREATE TABLE `products` (
77  `id` int(11) NOT NULL AUTO_INCREMENT,
78  `price` int(9) DEFAULT '0',
79  `name` varchar(64) NOT NULL,
80  `description` varchar(120) DEFAULT NULL,
81  `manufacturer` int(11) DEFAULT NULL,
82  PRIMARY KEY (`id`),
83  KEY `manufacturer` (`manufacturer`),
84  CONSTRAINT `products_ibfk_2` FOREIGN KEY (`manufacturer`) REFERENCES `manufacturers` (`id`)
85) ENGINE=InnoDB DEFAULT CHARSET=utf8;