需要打印车间数据库架构设计产品选项的帮助

Question

我最近一直在打印店的网站上工作。现在，我在开发一个高效的数据库模式时遇到了麻烦。

我的应用程序是一个为海报、名片和传单等提供自定义打印的Printshop。我面临的问题是开发产品选项的模式。这里有一个场景:一张名片可以有“尺寸”和“材料”选项。"Size“可以是"3.5x2.5英寸”或"3.25x2.25英寸“。类似地，“材料”可以是“300GSM卡库存”或“200GSM卡库存”。现在我的商店提供的是选项和数量组合的价格。

比如，100 Business Cards + 3.5x2.5 inch + 300 gsm Card Stock = $500.00

或

200 Business Cards + 3.5x2.5 inch + 300 gsm Card Stock = $800.00。

这里需要注意的一点是，“尺寸”选项对于“传单”和“海报”产品是不同的。因此，一个"3.5x2.5“的海报是没有意义的。海报产品将有自己的尺寸。价格总是与期权组合捆绑在一起，没有期权组合的产品没有单独的价格。

其次，还有基于重量的运输。所以我也想知道，在数据库中将权重存储在哪里？

请提供一些关于设计这样一个数据库的见解。此外，我想一个基于ActiveRecord的方法，因为我在EAV建模太弱。

Answer 1

您可以这样做：

Products

• ProductId主键，
• ProductName，
• ...

Materials

• MaterialID主键，
• MaterialName.

Units

• UnitId主键，
• UnitName.

ProductsSizesOptions

• SizeOptionId主键、
• Height，
• Width，
• UnitId外键约束REFERENCES Units(UnitId)为每个产品处理不同类型的单位。

ProductsMaterialOptions

• MaterialOptionId，
• Quantity，
• MaterialId外键约束REFERENCES Materials(MaterialId)为每个产品的报价处理不同类型的物料。

ProductsOffers

• OfferId主键、
• ProductId外键约束REFERENCES Products(ProductId)、
• SizeOptionId外键约束引用(MaterialOptionId)`、
• Price.

外键约束REFERENCES Products(ProductId)、

• SizeOptionId外键约束REFERENCES ProductsSizesOptions(SizeOptionId)

​

​

例如，对于您在问题中发布的示例数据，您可以通过在表格中执行JOIN操作来简单地获取每个产品的这些报价：

SELECT
  po.OfferId,
  p.ProductNAme,
  mo.Quantity,
  m.MaterialName,
  so.Height,
  so.width,
  u.UnitName,
  po.Price
FROM products                      AS p
INNER JOIN ProductsOffers          AS po ON p.ProductId         = po.ProductId
INNER JOIN ProductsMaterialOptions AS mo ON po.MaterialOptionId = mo.MaterialOptionId
INNER JOIN ProductsSizesOptions    AS so ON so.SizeOptionId     = po.SizeOptionId
INNER JOIN Units                   AS u  ON u.UnitId            = so.unitId
INNER JOIN Materials               AS m  ON m.MaterialId        = m.MaterialId;

这将为您提供类似以下内容：

| OFFERID |   PRODUCTNAME | QUANTITY | MATERIALNAME | HEIGHT | WIDTH | UNITNAME | PRICE |
-----------------------------------------------------------------------------------------
|       1 | Business Card |      100 |   Card Stock |      4 |     3 |      gsm |   500 |
|       2 | Business Card |      200 |   Card Stock |      4 |     3 |      gsm |   800 |

然后，您可以从前端应用程序中以您希望的方式格式化这些结果。

SQL Fiddle Demo For DB schema

更新

Products**:**

• ProductId，
• ProductName，
• ...

Options**:**

• OptionId，
• OptionName.

要存储可能的选项：

OptionId OptionName
1 Material
2 Shape
and so on

Properties**:**

• PropertyId，
• PropertyName，
• PropertyTypeId.

PropertiesTypes**:**

• PropertyTypeId，
• PropertyTypeName.

您可能不需要这个表，但是您可以在前端应用程序中使用它，以了解如何在应用程序上显示此字段。例如，它可能包含以下值：

1 Integer
2 String
3 Decimal
...

OptionsProperties**:**

• OptionPropertyId，
• OptionId，
• PropertyId.

每个选项的属性，例如Shape和Material

OptionPropertyId OptionId PropertyId 
1                   1        1
2                   2        2
3                   2        3
4                   2        4

ProductOptions**:**

• ProductOptionId，
• ProductId，
• OptionId.

ProductOptionsValues**:**

• ProductOfferOptionsId，
• ProductId，
• PropertyId，
• NumericValue，
• TXTValue.

ProductsOffers**:**

• OfferId，
• ProductOfferOptionsId，
• Quantity，
• Price.

​

​

因此，您可以获得每个产品的优惠列表，如下所示：

SELECT
  p.ProductName,
  MAX(CASE WHEN pr.PropertyName = 'Material Name' THEN PropertyValue END) AS 'Material Name',
  MAX(CASE WHEN pr.PropertyName = 'Height' THEN PropertyValue END) AS 'Height',
  MAX(CASE WHEN pr.PropertyName = 'Width' THEN PropertyValue END) AS 'Width',
  MAX(CASE WHEN pr.PropertyName = 'Unit' THEN PropertyValue END) AS 'Unit',
  o.Quantity,
  o.Price
FROM products AS p
INNER JOIN
(
  SELECT 
    ProductId,
    PropertyId,
    ProductOfferOptionsId, 
    COALESCE(NumericValue, TXTValue) AS PropertyValue
  FROM 
  ProductOptionsValues
) AS ov ON ov.ProductId = p.ProductId
INNER JOIN OptionsProperties AS op ON op.PropertyId            = ov.PropertyId
INNER JOIN Properties        AS pr ON op.PropertyId            = pr.PropertyId
INNER JOIN ProductsOffers    AS o  ON o.ProductOfferOptionsId  = ov.ProductOfferOptionsId 
GROUP BY p.ProductName,
         o.Quantity,
         o.Price;

这将为您提供：

|   PRODUCTNAME |  MATERIAL NAME | HEIGHT | WIDTH | UNIT | QUANTITY | PRICE |
-----------------------------------------------------------------------------
| Business Card | gsm Card Stock |    3.5 |   2.5 | inch |      100 |   500 |
| Business Card | gsm Card Stock |    3.5 |   2.5 | inch |      200 |   800 |

SQL Fiddle Demo

当然，这个查询没有任何意义。要获得所有属性的列表，必须使用动态SQL动态执行此操作。您可以将以下代码放入存储过程中：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(pr.PropertyName = ''',
      pr.PropertyName, ''', ov.PropertyValue, 0)) AS ', '''',   pr.PropertyName , '''')
  ) INTO @sql
FROM Properties        AS pr 
INNER JOIN
(
  SELECT 
    PropertyId,
    COALESCE(NumericValue, TXTValue) AS PropertyValue
  FROM 
  ProductOptionsValues
) AS ov ON pr.PropertyId = ov.PropertyId;

SET @sql = CONCAT('SELECT
  p.ProductName, ', @sql , ', o.Quantity,
  o.Price
FROM products AS p
INNER JOIN
(
  SELECT 
    ProductId,
    PropertyId,
    ProductOfferOptionsId, 
    COALESCE(NumericValue, TXTValue) AS PropertyValue
  FROM 
  ProductOptionsValues
) AS ov ON ov.ProductId = p.ProductId
INNER JOIN OptionsProperties AS op ON op.PropertyId            = ov.PropertyId
INNER JOIN Properties        AS pr ON op.PropertyId            = pr.PropertyId
INNER JOIN ProductsOffers    AS o  ON o.ProductOfferOptionsId  = ov.ProductOfferOptionsId 
GROUP BY   p.ProductName,
  o.Quantity,
  o.Price');



prepare stmt 
FROM @sql;

execute stmt;

Updated SQL Fiddle Demo

这将动态地透视所有属性。

Answer 2

您正在销售目录中的项目。商品既可以是商品(它实际上是产品的规格，而不是物理资产)，也可以是商品的集合，称为营销包。

您需要在这里使用Table Inheritance。

catalog_item
------------
id
type {good, marketing_package}
name

good : catalog_item
-------------------
size
material

marketing_package : catalog_item
--------------------------------
standard_price

marketing_package_good (this is a junction table)
----------------------
marketing_package_id
good_id
quantity

poster : good
-------------
{any poster-specific properties or defaults}


flyer : good
------------
{any flyer-specific properties or defaults}


business_card : good
--------------------
{any business-card-specific properties or defaults}