postgresql - 多单元多属性数据库设计

Question

假设我有这个我研究过的数据库设计。

Table: Products
ProductId | Name     | BaseUnitId
1         | Lab gown | 1
2         | Gloves   | 1
FK: BaseUnitId references Units.UnitId

Table: Units
UnitId | Name
1      | Each / Pieces
2      | Dozen
3      | Box

Table: Unit Conversion
ProdID | BaseUnitID | Factor | ConvertToUnitID
1      | 1          |  12    | 2
2      | 1          | 100    | 3
FK: BaseUnitId references Units.UnitID
FK: ConvertToUnitId references Units.UnitID

Table: Product Attribute
AttribId | Prod_ID | Attribute | Value
1        | 1       | Color     | Blue
2        | 1       | Size      | Large
3        | 2       | Color     | Violet
4        | 2       | Size      | Small
5        | 2       | Size      | Medium
6        | 2       | Size      | Large
7        | 2       | Color     | White
FK: Prod_ID references Product.ProductID

Table: Inventory
Prod_ID  | Base Unit Qty | Expiry
1        | 12            | n/a
2        | 100           | 2020-01-01 
2        | 100           | 2021-12-31
FK: Prod_ID references Product.ProductID

如何按属性分割每个单位的库存？

例如，我怎样才能得到小紫罗兰色手套的库存？大白手套？

有什么建议吗？我的想法是创建另一个表，将链接产品单位、产品属性和数量。

但我不知道如何将尺寸属性和颜色属性链接到一个单位。

最后，这个设计有什么问题吗？

Answer 1

我认为将产品的属性拆分到不同的表中是非常错误的。我理解正常化的愿望，但应该以不同的方式进行。

我会像这样处理产品及其属性:

CREATE TABLE product (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   name text NOT NULL,
   baseunit_id bigint NOT NULL REFERENCES unit
);

CREATE TABLE inventory (
   id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   product_id bigint NOT NULL REFERENCES product,
   color integer REFERENCES product_color,
   size integer REFERENCES product_size,
   other_attributes jsonb
);

如果您用自然语言来思考，这也是有道理的:“我们店里有多少双大号蓝色手套？”

不适用于特定产品的属性可以保留NULL。

我区分了常见属性和稀有属性。公共(public)属性有自己的列。稀有属性集中在 jsonb 列中。我知道后者既不规范也不漂亮，但不同的属性不太适合关系模型。列上的 GIN 索引将使搜索更加高效。