hi there,
I am a casual database tinkerer that has to build a new database.
so please do not fall down laughing if I ask stupid questions ..
the problem at hand is that I want to create tables with the following
structure:
- suppliers
they produce/deal-with 0 to n products
- products
a product is produced/sold by 1-n suppliers
it can have 0-1 declaration
- declarations
these are product specsheets
my questions are now:
what indices and actions do I have to create to link these tables ?
in the following script created by by MySQL-workbench. there is the
table tblProducts_has_tblSupplier which is linked to both tblSupplier and
tblProducts by foreign keys.
How do I guaranty integrity when deleting a product or supplier??
do i have to do that using triggers?
thanks for your insigth
robert
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 `energie` ;
USE `energie`;
-- -----------------------------------------------------
-- Table `energie`.`tblProducts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `energie`.`tblProducts` ;
CREATE TABLE IF NOT EXISTS `energie`.`tblProducts` (
`idtblProducts` INT(11) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`simplesystem` TINYINT(4) NULL DEFAULT 0 ,
`kompaktenergiezentrale` TINYINT(4) NULL DEFAULT 0 ,
`device_with_humidity_recovery` TINYINT(4) NULL DEFAULT 0 ,
`room_ventilator` TINYINT(4) NULL DEFAULT 0 ,
`comfort_regulation` TINYINT(4) NULL DEFAULT 0 ,
`heat_pump` TINYINT(4) NULL DEFAULT 0 ,
`outlet` TINYINT(4) NULL DEFAULT 0 ,
`air_dispersing_system` TINYINT(4) NULL DEFAULT 0 ,
`heat_exchanger` TINYINT(4) NULL DEFAULT 0 ,
`heat_recovery` TINYINT(4) NULL DEFAULT 0 ,
`humidity_recovery` TINYINT(4) NULL DEFAULT 0 ,
`sound_suppressor` TINYINT(4) NULL DEFAULT 0 ,
`pollen_filter` TINYINT(4) NULL DEFAULT 0 ,
`groundsregister` TINYINT(4) NULL DEFAULT 0 ,
`single_room` TINYINT(4) NULL DEFAULT 0 ,
`one_family_house` TINYINT(4) NULL DEFAULT 0 ,
`multi_family_house` TINYINT(4) NULL DEFAULT 0 ,
PRIMARY KEY (`idtblProducts`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `energie`.`tblDeclaration_vent`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `energie`.`tblDeclaration_vent` ;
CREATE TABLE IF NOT EXISTS `energie`.`tblDeclaration_vent` (
`idtblDeclaration_vent` INT NOT NULL AUTO_INCREMENT ,
`manufacturer` VARCHAR(45) NOT NULL ,
`name` VARCHAR(45) NOT NULL ,
`flowrate` INT NULL DEFAULT 0 ,
`humidity_recovery` INT NULL DEFAULT 1 ,
`energy_class` VARCHAR(1) NOT NULL ,
`energy_class_value` VARCHAR(10) NOT NULL ,
`hygiene_class` VARCHAR(1) NOT NULL ,
`noise_class` VARCHAR(1) NOT NULL ,
`audited_by` VARCHAR(45) NULL DEFAULT '' ,
`audited_date` DATE NULL DEFAULT NULL ,
`autition_report` VARCHAR(45) NULL DEFAULT '0-0-0' ,
`picture_name` VARCHAR(45) NULL DEFAULT '' ,
`remarks` TEXT NULL DEFAULT NULL ,
`reindex` BOOLEAN NULL DEFAULT 1 ,
`tblProducts_idtblProducts` INT(11) NULL ,
PRIMARY KEY (`idtblDeclaration_vent`) ,
CONSTRAINT `fk_tblDeclaration_vent_tblProducts`
FOREIGN KEY (`tblProducts_idtblProducts` )
REFERENCES `energie`.`tblProducts` (`idtblProducts` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'table with declaration data for ventilation'
PACK_KEYS = 1;
CREATE INDEX imanufacturer ON `energie`.`tblDeclaration_vent`
(`manufacturer` ASC) ;
CREATE INDEX ienergy_class ON `energie`.`tblDeclaration_vent`
(`energy_class` ASC) ;
CREATE INDEX ihygiene_class ON `energie`.`tblDeclaration_vent`
(`hygiene_class` ASC) ;
CREATE INDEX inoise_class ON `energie`.`tblDeclaration_vent`
(`noise_class` ASC) ;
CREATE UNIQUE INDEX imanufaturer_name ON `energie`.`tblDeclaration_vent`
(`manufacturer` ASC, `name` ASC) ;
CREATE INDEX iname ON `energie`.`tblDeclaration_vent` (`name` ASC) ;
CREATE INDEX fk_tblDeclaration_vent_tblProducts ON
`energie`.`tblDeclaration_vent` (`tblProducts_idtblProducts` ASC) ;
-- -----------------------------------------------------
-- Table `energie`.`tblSupplier`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `energie`.`tblSupplier` ;
CREATE TABLE IF NOT EXISTS `energie`.`tblSupplier` (
`idtblSupplier` INT(11) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`extraname` VARCHAR(45) NOT NULL ,
`description` VARCHAR(45) NOT NULL ,
`address` VARCHAR(45) NOT NULL ,
`extraaddress` VARCHAR(45) NOT NULL ,
`zip` INT(20) NOT NULL ,
`city` VARCHAR(45) NOT NULL ,
`pob` INT(20) NOT NULL ,
`country` VARCHAR(45) NOT NULL ,
`language` VARCHAR(45) NOT NULL ,
`email` VARCHAR(45) NOT NULL ,
`url` VARCHAR(60) NOT NULL ,
`phone` VARCHAR(45) NOT NULL ,
`fax` VARCHAR(45) NOT NULL ,
`responsibleperson` VARCHAR(45) NOT NULL ,
`companylogo` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`idtblSupplier`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
-- -----------------------------------------------------
-- Table `energie`.`tblProducts_has_tblSupplier`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `energie`.`tblProducts_has_tblSupplier` ;
CREATE TABLE IF NOT EXISTS `energie`.`tblProducts_has_tblSupplier` (
`tblProducts_idtblProducts` INT(11) NOT NULL ,
`tblSupplier_idtblSupplier` INT(11) NOT NULL ,
PRIMARY KEY (`tblProducts_idtblProducts`, `tblSupplier_idtblSupplier`) ,
CONSTRAINT `fk_tblProducts_has_tblSupplier_tblProducts`
FOREIGN KEY (`tblProducts_idtblProducts` )
REFERENCES `energie`.`tblProducts` (`idtblProducts` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_tblProducts_has_tblSupplier_tblSupplier`
FOREIGN KEY (`tblSupplier_idtblSupplier` )
REFERENCES `energie`.`tblSupplier` (`idtblSupplier` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX fk_tblProducts_has_tblSupplier_tblProducts ON
`energie`.`tblProducts_has_tblSupplier` (`tblProducts_idtblProducts` ASC) ;
CREATE INDEX fk_tblProducts_has_tblSupplier_tblSupplier ON
`energie`.`tblProducts_has_tblSupplier` (`tblSupplier_idtblSupplier` ASC) ;
SET [EMAIL PROTECTED];
SET [EMAIL PROTECTED];
SET [EMAIL PROTECTED];
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
