[Conny Westh]

Ang frågan om hur du kan lagra olika egenskaper per kategori så kan du lösa det på det här sättet i SQL:

Jag har först lagt upp en databas som heter `wn`. Lägger SQL och DDL i detta inlägg, sen får vi ta kommentarer i separat inlägg.

För att Radera strukturen:

USE `wn`;

drop table wn.property_int;
drop table wn.article;
drop table wn.property_group;
drop table wn.category;
drop table wn.category_group;

För att skapa strukturen med DDL-script:

USE `wn`;

delimiter $$


CREATE TABLE `category_group` (
  `category_group_id` int(11) NOT NULL,
  `category_group_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`category_group_id`)
) ENGINE=InnoDB $$



CREATE TABLE `category` (
  `category_id` int(11) NOT NULL,
  `category_group` int(11) DEFAULT NULL,
  `category_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`category_id`),
  KEY `category_category_group_idx` (`category_group`),
  CONSTRAINT `category_category_group` FOREIGN KEY (`category_group`) REFERENCES `category_group` (`category_group_id`)
) ENGINE=InnoDB $$


CREATE TABLE `article` (
  `article_id` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `article_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`article_id`),
  KEY `article_category_idx` (`category_id`),
  CONSTRAINT `article_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE=InnoDB $$



CREATE TABLE `property_group` (
  `category_id` int(11) NOT NULL,
  `property_group_name` varchar(30) NOT NULL,
  PRIMARY KEY (`category_id`,`property_group_name`),
  KEY `property_group_category_idx` (`category_id`),
  CONSTRAINT `property_group_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE=InnoDB $$



CREATE TABLE `property_int` (
  `prop_article_id` int(11) NOT NULL,
  `prop_category_id` int(11) NOT NULL,
  `prop_gr_name` varchar(30) NOT NULL,
  `property_int_value` int(11) DEFAULT NULL,
  PRIMARY KEY (`prop_article_id`,`prop_category_id`,`prop_gr_name`),
  KEY `prop_article_idx` (`prop_article_id`),
  KEY `prop_cat_idx` (`prop_category_id`,`prop_gr_name`),
  CONSTRAINT `prop_article` FOREIGN KEY (`prop_article_id`) REFERENCES `article` (`article_id`),
  CONSTRAINT `prop_cat_gr` FOREIGN KEY (`prop_category_id`, `prop_gr_name`) REFERENCES `property_group` (`category_id`, `property_group_name`)
) ENGINE=InnoDB
$$

DELIMITER ;

DROP procedure IF EXISTS `FillProperty`;



DELIMITER $$

USE `wn`$$

CREATE PROCEDURE `wn`.`FillProperty` ()

BEGIN

	INSERT IGNORE wn.property_int (prop_article_id, prop_category_id, prop_gr_name)

	select a.article_id, pg.category_id, pg.property_group_name 

	from wn.property_group pg, wn.article a

	where a.category_id = pg.category_id;



END



$$

DELIMITER ;

För att addera lite testdata:

/*
-- Query: SELECT * FROM wn.category_group
LIMIT 0, 1000

-- Date: 2013-03-03 00:10
*/
INSERT IGNORE `category_group` (`category_group_id`,`category_group_name`) VALUES (1,'Fordon');
INSERT IGNORE `category_group` (`category_group_id`,`category_group_name`) VALUES (2,'Elektronik');



/*
-- Query: SELECT * FROM wn.category
LIMIT 0, 1000

-- Date: 2013-03-03 00:20
*/
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (1,1,'Bilar');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (2,1,'Bildelar');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (3,1,'Båtar');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (4,1,'Båtdelar');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (5,2,'Datorer');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (6,2,'Musik');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (7,2,'Ljud');
INSERT IGNORE `category` (`category_id`,`category_group`,`category_name`) VALUES (8,2,'Bild');




/*
-- Query: SELECT * FROM wn.property_group
LIMIT 0, 1000

-- Date: 2013-03-03 00:14
*/
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (1,'Mätarställning');
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (1,'Tjänstevikt (Kg)');
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (1,'Årsmodell');
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (5,'Antal kärnor (CPU)');
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (5,'CPU Hastighet (Mhz)');
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (5,'Installerat Primärminne (MB)');
INSERT IGNORE `property_group` (`category_id`,`property_group_name`) VALUES (5,'Maximalt Primärminne (MB)');



/*
-- Query: SELECT * FROM wn.article
LIMIT 0, 1000

-- Date: 2013-03-03 00:26
*/
INSERT IGNORE `article` (`article_id`,`category_id`,`article_name`) VALUES (1,1,'Volvo V70');
INSERT IGNORE `article` (`article_id`,`category_id`,`article_name`) VALUES (2,5,'DELL Latitude');
INSERT IGNORE `article` (`article_id`,`category_id`,`article_name`) VALUES (3,2,'Kofångare');
INSERT IGNORE `article` (`article_id`,`category_id`,`article_name`) VALUES (4,2,'Fälgkors');
INSERT IGNORE `article` (`article_id`,`category_id`,`article_name`) VALUES (5,3,'Coronet Master');
INSERT IGNORE `article` (`article_id`,`category_id`,`article_name`) VALUES (6,4,'Rodersprint');