从库存数据库高效地查询产品变体(避免eav)

我正在建立一个有趣的电子商务网站与三个主要组成部分：前端在Angular ，mysql数据库和php后端，将作为restapi的功能。
库存数据库由产品组成，这些产品有许多变体（是的，我在stackexchange上看到过许多讨论此类问题的问题，但我还没有找到令人满意的解决方案）。在读了几个关于使用eav方法的恐怖故事之后，我避免了这个问题。
我想我已经做了一个数据库解决方案，但我不确定我应该如何查询数据。由于存在多对多关系，因此对一个产品及其所有变体的查询可以返回几行，其中只有几列包含“new”数据。
（tl:dr）主要问题：当查询产品的所有变体（颜色、可用颜色的大小和颜色/大小变体的数量）时，最有效的方法是：
执行1个数据库查询，返回需要在php中构建关联数组的许多行，丢弃“重复”数据？
检索产品的所有变体时执行多个查询？
或者数据库设计中是否存在导致这些问题的缺陷？
数据库架构：

CREATE TABLE IF NOT EXISTS `products` (
  `productID` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `longDescription` TEXT,
  `shortDescription` VARCHAR(1000),
  PRIMARY KEY (`productID`)
);
INSERT INTO `products` (`productID`, `name`, `longDescription`, `shortDescription`) VALUES 
(1, 'A shirt', 'Long description of this product', 'shortDesc of shirt');

CREATE TABLE IF NOT EXISTS `productpricing` (
  `productID` INT(11) NOT NULL,
  `startDate` TIMESTAMP NOT NULL DEFAULT '1971-01-01 00:00:00',
  `endDate` TIMESTAMP NOT NULL DEFAULT '2099-01-01 00:00:00',
  `price` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY(`productID`, `endDate`),
  FOREIGN KEY (`productID`) REFERENCES products(`productID`) 
);
INSERT INTO `productpricing` (`productID`, `startDate`, `endDate`, `price`) VALUES
(1, '1971-01-01 00:00:00', '2099-01-01 00:00:00', 309.99);

CREATE TABLE IF NOT EXISTS `categories` (
  `categoryID` INT(11) NOT NULL AUTO_INCREMENT,
  `categoryName` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`categoryID`)
);
INSERT INTO `categories` (`categoryID`, `categoryName`) VALUES
(1, 'Test Category');

CREATE TABLE IF NOT EXISTS `sizes` (
  `sizeID` INT(11) NOT NULL,
  `size` INT NOT NULL,
  PRIMARY KEY (`sizeID`)
);
INSERT INTO `sizes` (`sizeID`, `size`) VALUES
(1, 50),
(2, 56),
(3, 62),
(4, 68),
(5, 74),
(6, 80),
(7, 86);

CREATE TABLE IF NOT EXISTS `colors` (
  `colorID` INT(11) NOT NULL,
  `color` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`colorID`)
);
INSERT INTO `colors` (`colorID`, `color`) VALUES
(1, "Red"),
(2, "White"),
(3, "Blue"),
(4, "Purple");

CREATE TABLE IF NOT EXISTS `product_variants` (
  `productvariantID` INT(11) NOT NULL,
  `productID` INT(11) NOT NULL,
  `categoryID` INT(11) NOT NULL,
  `colorID` INT(11) NOT NULL,
  `sizeID` INT(11) NOT NULL,
  `sku` VARCHAR(50) NOT NULL UNIQUE,
  `quantity` INT(11) NOT NULL,
  `isActive` BOOLEAN NOT NULL DEFAULT 0,
  PRIMARY KEY (`productvariantID`),
  UNIQUE (`productID`, `colorID`, `sizeID`),
  FOREIGN KEY (`productID`) REFERENCES products(`productID`),
  FOREIGN KEY (`categoryID`) REFERENCES categories(`categoryID`),
  FOREIGN KEY (`colorID`) REFERENCES colors(`colorID`),
  FOREIGN KEY (`sizeID`) REFERENCES sizes(`sizeID`)
);
INSERT INTO `product_variants` (`productvariantID`, `productID`, `categoryID`, `colorID`, `sizeID`, `sku`, `quantity`, `isActive`) VALUES
(1, 1, 1, 2, 1, 'clalb121', 2, 1),
(2, 1, 1, 3, 2, 'clalb132', 1, 1),
(3, 1, 1, 2, 2, 'clalb122', 5, 1);

CREATE TABLE IF NOT EXISTS `images` (
  `imageID` INT(11) NOT NULL AUTO_INCREMENT,
  `imageFilename` VARCHAR(100) NOT NULL,
  PRIMARY KEY(`imageID`)
);
INSERT INTO `images` (`imageID`, `imageFilename`) VALUES
(1, 'shirtwhite1.jpg'),
(2, 'shirtwhite2.jpg'),
(3, 'shirtblue1.jpg'),
(4, 'shirtblue2.jpg');

CREATE TABLE IF NOT EXISTS `product_variant_images` (
  `productvariantID` INT(11) NOT NULL,
  `imageID` INT(11) NOT NULL,
  FOREIGN KEY(`productvariantID`) REFERENCES  product_variants(`productvariantID`),
  FOREIGN KEY(`imageID`) REFERENCES images(`imageID`)
);
INSERT INTO `product_variant_images` (`productvariantID`, `imageID`) VALUES
(1, 1),
(1, 2),
(2, 3),
(2, 4),
(3, 1),
(3, 2);

（我将从产品变体表中删除categoryid，因为同一产品的不同变体没有不同的类别。）
查询1个产品的所有变体的示例（在一个查询中）：

SELECT
    p.productID,
    p.name,
    p.longDescription,
    p.shortDescription,
    colors.color,
    sizes.size,
    pvar.quantity,
    pprice.price,
    images.imageFilename
FROM products as p
JOIN product_variants as pvar
     ON p.productID = pvar.productID
JOIN productpricing as pprice
    ON pprice.productID = p.productID
JOIN colors 
    ON colors.colorID = pvar.colorID
JOIN sizes
    ON sizes.sizeID = pvar.sizeID
JOIN product_variant_images as pvari
    ON pvari.productvariantID = pvar.productvariantID
JOIN images 
    ON images.imageID = pvari.imageID
WHERE p.productID = 1 AND pvar.isActive = 1 AND NOW() BETWEEN pprice.startDate AND pprice.endDate;

返回集示例：


# productID, name, longDescription, shortDescription, color, size, quantity, price, imageFilename

'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '50', '2', '309.99', 'shirtwhite1.jpg'
'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '50', '2', '309.99', 'shirtwhite2.jpg'
'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '56', '5', '309.99', 'shirtwhite1.jpg'
'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '56', '5', '309.99', 'shirtwhite2.jpg'
'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'Blue', '56', '1', '309.99', 'shirtblue1.jpg'
'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'Blue', '56', '1', '309.99', 'shirtblue2.jpg'

从结果集中可以看出，衬衫的白色变体（一种尺寸）与两个图像相关联，因此有两个记录，其中只有imagefilename包含“新数据”。
使用多个查询的示例：

SELECT 
    p.productID,
    p.name,
    p.longDescription,
    p.shortDescription,
    pprice.price
FROM products as p
JOIN productpricing as pprice
    ON pprice.productID = p.productID
WHERE p.productID = 1 AND NOW() BETWEEN pprice.startDate AND pprice.endDate;

SELECT
    c.color,
    s.size,
    pvar.quantity
FROM product_variants as pvar
JOIN colors AS c
    ON c.colorID = pvar.colorID
JOIN sizes as s
    ON s.sizeID = pvar.sizeID
WHERE pvar.productID = 1;

SELECT DISTINCT
    c.color,
    i.imageFilename
FROM images  AS i
JOIN product_variant_images as pvari
    ON pvari.imageID = i.imageID
JOIN product_variants as pvar
    ON pvar.productvariantID = pvari.productvariantID
JOIN colors AS c
    ON c.colorID = pvar.colorID
WHERE pvar.productID = 1;

多个查询结果集：


# productID, name, longDescription, shortDescription, price

'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', '309.99'

# color, size, quantity

'White', '50', '2'
'Blue', '56', '1'
'White', '56', '5'

# color, imageFilename

'White', 'shirtwhite1.jpg'
'White', 'shirtwhite2.jpg'
'Blue', 'shirtblue1.jpg'
'Blue', 'shirtblue2.jpg'

任何关于数据库设计/查询的建议都将不胜感激！

从库存数据库高效地查询产品变体(避免eav)

暂无答案！

相关问题

热门标签

最新问答