help, optmization... 6 tables... 1 select!

[John Aust]

Hi! I'm working on an e-commerce project and need to put some flexibility to the system, the products can have attributes, categories (max 1 sublevel), images and types.

I do this, have one table for product, one for attribute, one for inventory (attribute<->product<->qty), one for images and one for types

Have this tables structures...

CREATE TABLE `product_attribute` (
`idproduct_attribute` int(11) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL,
PRIMARY KEY (`idproduct_attribute`)
) ENGINE=InnoDB;

CREATE TABLE `product_category` (
`idproduct_category` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` varchar(45) NOT NULL,
`category_slug` varchar(45) NOT NULL,
`category_father` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`idproduct_category`)
) ENGINE=InnoDB;

CREATE TABLE `product_image` (
`idproduct_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idproduct` int(10) unsigned NOT NULL,
`image_file` varchar(45) NOT NULL,
`sort` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`idproduct_image`,`idproduct`),
KEY `fk_product_image_product` (`idproduct`)
) ENGINE=InnoDB;

CREATE TABLE `product_inventory` (
`idproduct_inventory` int(10) unsigned NOT NULL AUTO_INCREMENT,
`qty` int(10) unsigned NOT NULL DEFAULT '0',
`total` int(10) unsigned NOT NULL DEFAULT '0',
`idproduct` int(10) unsigned NOT NULL,
`idcampaign` int(10) unsigned NOT NULL,
`idtype` int(10) unsigned NOT NULL,
`idcategory` int(10) unsigned NOT NULL,
`idsupplier` int(10) unsigned NOT NULL,
`idattribute` int(11) NOT NULL,
PRIMARY KEY (`idproduct_inventory`,`idproduct`,`idcampaign`,`idtype`,`idcategory`,`idsupplier`,`idattribute`) USING BTREE,
KEY `fk_product_inventory_product_attribute` (`idattribute`),
KEY `fk_product_inventory_product` (`idproduct`,`idcampaign`,`idtype`,`idcategory`,`idsupplier`) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE `product_type` (
`idproduct_type` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(45) NOT NULL,
`type_slug` varchar(45) NOT NULL,
PRIMARY KEY (`idproduct_type`)
) ENGINE=InnoDB;

CREATE TABLE `product` (
`idproduct` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idcampaign` int(10) unsigned NOT NULL,
`idtype` int(10) unsigned NOT NULL,
`idcategory` int(10) unsigned NOT NULL,
`idsupplier` int(10) unsigned NOT NULL,
`price` decimal(8,2) NOT NULL DEFAULT '0.00',
`market_price` decimal(8,2) NOT NULL DEFAULT '0.00',
`description` text NOT NULL,
`quick_description` text NOT NULL,
`title` varchar(255) NOT NULL,
`attribute_name` varchar(45) NOT NULL,
`slug` varchar(255) NOT NULL,
`id_at_suplier` varchar(45) NOT NULL,
`sort` tinyint(3) unsigned NOT NULL,
`rand` varchar(12) NOT NULL,
PRIMARY KEY (`idproduct`,`idcampaign`,`idtype`,`idcategory`,`idsupplier`),
KEY `fk_product_campaign` (`idcampaign`),
KEY `fk_product_product_type` (`idtype`),
KEY `fk_product_product_category` (`idcategory`),
KEY `fk_product_supplier1` (`idsupplier`)
) ENGINE=InnoDB;

and, to select the product i'm using this...

SELECT `product`.*, `product_type`.`type`, `product_type`.`type_slug`, SUM(product_inventory.qty) AS `qty`, SUM(product_inventory.total) AS `total`, (SELECT `product_image`.`image_file` FROM `product_image` WHERE (product_image.idproduct = product.idproduct) ORDER BY `sort` ASC LIMIT 1) AS `image` FROM `product`
INNER JOIN `product_type` ON product_type.idproduct_type = product.idtype
INNER JOIN `product_inventory` ON product_inventory.idproduct = product.idproduct WHERE (product.idcampaign = 1) AND (product.idcategory IN(SELECT `product_category`.`idproduct_category` FROM `product_category` WHERE (product_category.idproduct_category = 1) OR (product_category.category_father = 1))) GROUP BY `product`.`idproduct` ORDER BY `type` DESC, `qty` DESC, `product`.`sort` ASC

it return the category name, type, quantity, total, first image and i can select by level of category... can anyone tell if it is crap trash or right?

sorry my poor english!

thx advice!