· 10 years ago · Sep 25, 2016, 08:50 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`types`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`types` (
21 `type_id` INT NOT NULL AUTO_INCREMENT,
22 `type_name` VARCHAR(64) NOT NULL,
23 `type_canonical` VARCHAR(64) NULL,
24 PRIMARY KEY (`type_id`),
25 UNIQUE INDEX `type_name_UNIQUE` (`type_name` ASC),
26 UNIQUE INDEX `type_canonical_UNIQUE` (`type_canonical` ASC))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`attribute_types`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`attribute_types` (
34 `attribute_type_id` INT NOT NULL,
35 `attribute_type_name` VARCHAR(64) NOT NULL,
36 PRIMARY KEY (`attribute_type_id`),
37 UNIQUE INDEX `attribute_type_name_UNIQUE` (`attribute_type_name` ASC))
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table `mydb`.`attributes`
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS `mydb`.`attributes` (
45 `attribute_id` INT UNSIGNED NOT NULL,
46 `types_type_id` INT UNSIGNED NOT NULL,
47 `attribute_type` INT NOT NULL,
48 `attribute_max_length` INT UNSIGNED NULL,
49 PRIMARY KEY (`attribute_id`, `types_type_id`),
50 INDEX `fk_attributes_types_idx` (`types_type_id` ASC),
51 INDEX `fk_attributes_attribute_types1_idx` (`attribute_type` ASC),
52 CONSTRAINT `fk_attributes_types`
53 FOREIGN KEY (`types_type_id`)
54 REFERENCES `mydb`.`types` (`type_id`)
55 ON DELETE NO ACTION
56 ON UPDATE NO ACTION,
57 CONSTRAINT `fk_attributes_attribute_types1`
58 FOREIGN KEY (`attribute_type`)
59 REFERENCES `mydb`.`attribute_types` (`attribute_type_id`)
60 ON DELETE NO ACTION
61 ON UPDATE NO ACTION)
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `mydb`.`resources`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `mydb`.`resources` (
69 `resource_id` INT NOT NULL,
70 `resource_type` INT NOT NULL,
71 `resource_name` VARCHAR(64) NULL,
72 `recource_canonical` VARCHAR(64) NULL,
73 PRIMARY KEY (`resource_id`),
74 UNIQUE INDEX `recource_canonical_UNIQUE` (`recource_canonical` ASC),
75 INDEX `fk_resources_types1_idx` (`resource_type` ASC),
76 CONSTRAINT `fk_resources_types1`
77 FOREIGN KEY (`resource_type`)
78 REFERENCES `mydb`.`types` (`type_id`)
79 ON DELETE NO ACTION
80 ON UPDATE NO ACTION)
81ENGINE = InnoDB;
82
83
84-- -----------------------------------------------------
85-- Table `mydb`.`resource_attributes`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS `mydb`.`resource_attributes` (
88 `res_atr_id` INT UNSIGNED NOT NULL,
89 `res_atr_resource` INT NOT NULL,
90 `res_atr_value` VARCHAR(256) NOT NULL,
91 PRIMARY KEY (`res_atr_id`, `res_atr_resource`),
92 INDEX `fk_resource_attributes_resources1_idx` (`res_atr_resource` ASC),
93 INDEX `fk_resource_attributes_attributes1_idx` (`res_atr_id` ASC),
94 CONSTRAINT `fk_resource_attributes_resources1`
95 FOREIGN KEY (`res_atr_resource`)
96 REFERENCES `mydb`.`resources` (`resource_id`)
97 ON DELETE NO ACTION
98 ON UPDATE NO ACTION,
99 CONSTRAINT `fk_resource_attributes_attributes1`
100 FOREIGN KEY (`res_atr_id`)
101 REFERENCES `mydb`.`attributes` (`attribute_id`)
102 ON DELETE NO ACTION
103 ON UPDATE NO ACTION)
104ENGINE = InnoDB;
105
106
107SET SQL_MODE=@OLD_SQL_MODE;
108SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
109SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;