w0at0pWm

1CREATE DATABASE IF NOT EXISTS `shopdb`
2USE `shopdb`;
3
4CREATE TABLE IF NOT EXISTS `category` (
5  `CategoryID` int(11) NOT NULL AUTO_INCREMENT,
6  `Name` varchar(20) NOT NULL,
7  `Path` varchar(100) NOT NULL DEFAULT 'images\\categories\\no-image.jpg',
8  PRIMARY KEY (`CategoryID`)
9) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
10
11CREATE TABLE IF NOT EXISTS `good` (
12  `GoodID` int(11) NOT NULL AUTO_INCREMENT,
13  `Price` int(11) NOT NULL,
14  `Name` varchar(50) NOT NULL,
15  `About` varchar(1000) NOT NULL,
16  `Path` varchar(100) NOT NULL,
17  `CategoryID` int(11) NOT NULL,
18  PRIMARY KEY (`GoodID`),
19  KEY `FK_book_category` (`CategoryID`),
20  CONSTRAINT `FK_good_category` FOREIGN KEY (`CategoryID`) REFERENCES `category` (`CategoryID`) ON DELETE CASCADE ON UPDATE CASCADE
21) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
22
23CREATE TABLE IF NOT EXISTS `order` (
24  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
25  `Date` date NOT NULL,
26  `Submited` bit(1) NOT NULL DEFAULT b'0',
27  `Delivered` bit(1) NOT NULL DEFAULT b'0',
28  `Number` int(11) NOT NULL DEFAULT '1',
29  `UserID` int(11) NOT NULL,
30  `GoodID` int(11) NOT NULL,
31  PRIMARY KEY (`OrderID`),
32  KEY `FK_order_user` (`UserID`),
33  KEY `FK_order_good` (`GoodID`),
34  CONSTRAINT `FK_order_good` FOREIGN KEY (`GoodID`) REFERENCES `good` (`GoodID`) ON DELETE CASCADE ON UPDATE CASCADE,
35  CONSTRAINT `FK_order_user` FOREIGN KEY (`UserID`) REFERENCES `user` (`UserID`) ON DELETE CASCADE ON UPDATE CASCADE
36) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
37
38CREATE TABLE IF NOT EXISTS `user` (
39  `UserID` int(11) NOT NULL AUTO_INCREMENT,
40  `Login` varchar(25) NOT NULL,
41  `Password` varchar(64) NOT NULL,
42  `Rights` varchar(15) NOT NULL,
43  `State` bit(1) NOT NULL,
44  `PhoneNo` varchar(20) NOT NULL,
45  PRIMARY KEY (`UserID`),
46  UNIQUE KEY `Login` (`Login`)
47) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;