|
Схема для системы управления товарами
#37663576
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Поругайте схему :) Нужно для системы управления складом т.е. добавление привезённого товара, редактирование свойств, "выставление" в интернет-магазины, учет клиентов и продаж. Интересует в основном аспект нормализации и бардак со связями (типами, изначально писал на SQL, потом сделал импорт в Workbench'е).
Схема:
SQL:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `wh` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `wh` ;
-- -----------------------------------------------------
-- Table `wh`.`tbl_desc_templates`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_desc_templates`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_desc_templates` (
`id` INT NOT NULL AUTO_INCREMENT,
`content` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_types`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_types`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_types` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(60) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_categories`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_categories`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_categories` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL ,
`parent_id` INT NULL ,
`ex_category_id` INT NOT NULL,
PRIMARY KEY (`id`) ,
CONSTRAINT `fk_parent_category`
FOREIGN KEY (`parent_id`)
REFERENCES `wh`.`tbl_categories` (`id` )
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_goods`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_goods`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_goods` (
`id` INT NOT NULL AUTO_INCREMENT ,
`part` VARCHAR(45) NOT NULL ,
`type_id` INT NOT NULL ,
`category_id` INT NOT NULL ,
`place` VARCHAR(45) NOT NULL ,
`title` VARCHAR(200) NOT NULL ,
`title_template` VARCHAR(200) NOT NULL ,
`color` VARCHAR(45) NOT NULL ,
`size` INT NOT NULL ,
`desc` TEXT NOT NULL ,
`desc_template_id` INT NULL ,
`price` FLOAT NOT NULL ,
`count` INT NOT NULL ,
`creator_id` INT NOT NULL ,
`create_time` DATETIME NOT NULL,
`editor_id` INT NOT NULL ,
`edit_time` DATETIME NOT NULL,
PRIMARY KEY (`id`, `type_id`, `category_id`) ,
CONSTRAINT `fk_goods_type`
FOREIGN KEY (`type_id` )
REFERENCES `wh`.`tbl_types` (`id` )
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_goods_category`
FOREIGN KEY (`category_id` )
REFERENCES `wh`.`tbl_categories` (`id` )
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_goods_desc_template`
FOREIGN KEY (`desc_template_id`)
REFERENCES `wh`.`tbl_desc_templates` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_goods_creator`
FOREIGN KEY (`creator_id`)
REFERENCES `wh`.`tbl_users` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_goods_editor`
FOREIGN KEY (`editor_id`)
REFERENCES `wh`.`tbl_users` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_incomings`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_incomings` (
`id` INT NOT NULL AUTO_INCREMENT,
`goods_id` INT NOT NULL,
`creator_id` INT NOT NULL,
`count` INT NOT NULL,
`incomings_date` DATETIME NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_incoming_goods`
FOREIGN KEY (`goods_id`)
REFERENCES `wh`.`tbl_goods` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_incomings_creator`
FOREIGN KEY (`creator_id`)
REFERENCES `wh`.`tbl_users` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_users`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_users` (
`id` INT NOT NULL AUTO_INCREMENT,
`login` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
`last_access` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`clients`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_clients`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_clients` (
`id` INT NOT NULL AUTO_INCREMENT,
`login` VARCHAR(60) NOT NULL,
`uid` INT NOT NULL,
`email` VARCHAR(100) NOT NULL,
`fio` VARCHAR(120) NOT NULL,
`company` VARCHAR(100),
`phone` VARCHAR(60),
`address` VARCHAR(120) NOT NULL,
`city` VARCHAR(60) NOT NULL,
`post_code` VARCHAR(30) NOT NULL,
`country` VARCHAR(60),
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_delivery_types`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_delivery_types`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_delivery_types` (
`id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(120) NOT NULL,
`cost` FLOAT NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_shops`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_shops`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_shops` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(60),
`last_update` DATETIME NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_sale_goods`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_sale_goods`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_sale_goods` (
`id` INT NOT NULL AUTO_INCREMENT,
`goods_id` INT NOT NULL,
`shop_id` INT NOT NULL,
`price` INT NOT NULL,
`count` INT NOT NULL,
`status` INT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_sale_goods_goods`
FOREIGN KEY (`goods_id`)
REFERENCES `wh`.`tbl_goods` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_sale_goods_shop`
FOREIGN KEY (`shop_id`)
REFERENCES `wh`.`tbl_shops` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `wh`.`tbl_orders`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `wh`.`tbl_orders`;
CREATE TABLE IF NOT EXISTS `wh`.`tbl_orders` (
`id` INT NOT NULL AUTO_INCREMENT,
`tid` VARCHAR(60) NOT NULL,
`date` DATETIME NOT NULL,
`goods_id` INT NOT NULL,
`cost` DOUBLE NOT NULL,
`delivery_type_id` INT NOT NULL,
`client_id` INT NOT NULL,
`tid` VARCHAR(60) NOT NULL,
`status` INT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_orders_goods`
FOREIGN KEY (`goods_id`)
REFERENCES `wh`.`tbl_sale_goods`
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_orders_delivery_type`
FOREIGN KEY (`delivery_type_id`)
REFERENCES `wh`.`tbl_delivery_types` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT `fk_orders_client`
FOREIGN KEY (`client_id`)
REFERENCES `wh`.`tbl_clients` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
|
|
|
