主页 / dba / 问题 / 273530
Accepted
Claudio Floreani
Asked: 2020-08-11 09:16:58 +0800 CST

如何在另一个表（关系）中存储对任何其他列（属性）的引用（包含一个记录）

  • 772

TL;DR：如果数据库模式应该包含所有业务逻辑，那么如何指定属性类型是对特定属性的引用，而不是特定记录（如外键的情况）？

举个例子，假设我有一个表，其中有一"Discounts"列"share"包含要应用于 column 或 table 值"cost"的百分比。"price""shipping""Items"

"Discounts"还拥有一个外键"item_id"。

我需要在"base"表"Discounts"中添加另一列来存储对 table 列之一的引用"Items"，并计算该列值的百分比。
例如，给定这些值：

Discounts
share    base                 item_id
-------------------------------------
50       (item's cost)        3
25       (item's price)       1
100      (item's shipping)    2


Items
id    cost    price    shipping
-------------------------------
1     10      40       20
2     55      60       30
3     50      85       10

我希望能够计算：

  • 50 的 50%（第 3 项的成本）
  • 40 的 25%（第 1 项的价格）
  • 30 的 100%（第 2 项的运输）

列“base”不应包含引用列的数字（例如 3）或名称（例如“price”），因为每个表的名称或顺序可能会更改。特别是数据库对列（属性）顺序或行（记录/元组）顺序没有任何了解，事实上，RDB 理论断言“关系的元组没有特定的顺序，而元组反过来, 对属性没有顺序。»

相反，如果我们依赖列名，我们应该强制每个条目包含一个有效的属性名，并且每当属性名更改时，我们必须更改其记录、约束和应用程序的验证。如果名称在多个关系中被引用，那么维护数据库完整性就变得非常复杂。

这里的问题是我们没有在数据库模式中写入对属性名称的引用（就像我们添加外键时一样），而是写入数据本身，这似乎是一种非常糟糕的做法，因为它威胁到引用完整性。

如果没有与数据库无关的方法来执行此操作，则假设数据库是 PostgreSQL (v12+)。

database-design postgresql

4 个回答

  1. 作为一个#database-design 问题（正如您所强调的那样），您走错了方向。进入关系世界的正确方法是设计数据库，使列名不会改变。幸运的是，PostgreSQL 有许多你可以使用的 noSQL 特性。

    另外，如果属性的数量不固定，不要将它们实现为一个表中的列，添加一个抽象级别。这可以通过添加一个或两个更多的表或使用 JSONB 来保存您的属性来完成。

    无论如何，这都会增加您的设计的复杂性，使得形成 ad hoq -queries 并在没有错误的情况下实现它变得更加困难。请再想一想，这才是你真正需要的。它当然可以完成，如果你不能没有它，但它需要一些时间和比你在这个问题中所能提供的更多的提前计划。

    如果您决定这样做，请使用FUNCTIONs 来计算折扣并使用VIEWs ，这样您就不必经常输入那些长查询。如果属性名称必须更改，请将它们映射到单独表中固定的内容。

    • 1
  2. Best Answer

    我认为您的模型错误地代表了您的用例。项目价值元素（成本、价格或运费）似乎本身就是一个实体。我的模型（不知道大局）可能看起来像这样：

    Items
------------------------
Id      | int      | PK
Name    | string   | 
... other attributes

ItemValueElements
------------------------
Item ID | int      | PK, FK
Type    | enum     | PK       -- one of: 'cost', 'price', 'shipping'
Value   | currency |

-- (Item ID, Type) would be the primary key here.

Discounts
------------------------
Item ID | int      | FK
Type    | enum     | FK
Share   | decimal  |

    实际上，折扣通常不适用于单个商品，而是适用于通过不同方式识别的某些类别的商品，并且您的实际模型会相应更改。

    如果对您而言并非如此，并且您确实希望每件商品都有折扣，您可以简单地将 ItemValueElements 和 Discounts 合并为一个实体。

    • 1

  3. 我不确定应该包含什么基础，但我会假设可以存储 C 作为成本，P 存储价格，S 存储运输？

    select id
     , cost - cost_rebate as cost
     , price - price_rebate as price
     , shipping - shipping_rebate as shipping
from (
    select i.id
         , case when d.base = 'C' 
                then d.share / 100.0 
                else 0 
           end * cost as cost_rebate
         , case when d.base = 'P' 
                then d.share / 100.0 
                else 0 
           end * price as price_rebate
         , case when d.base = 'S' 
                then d.share / 100.0 
                else 0 
           end * shipping as shipping_rebate
         , cost
         , price
         , shipping
    from items i
    join discounts d
        on i.id = d.item_id
) as tmp
    • 0

  4. （可能的解决方案，顺便说一下我不喜欢）

    为数据类型使用列

    根据@a_horse_with_no_name 的建议，使用列类型来描述引用的列，第一次尝试可以是 model cost，price并shipping使用两个列，一个包含列类型，另一个包含实际值。

    我们可以这样设计数据库：

    Discounts
share    base_type    item_id
------------------------------------------
50       C            3
25       P            1
100      S            2


Items
id    type_1    value_1    type_2    value_2    type_3    value_3
-----------------------------------------------------------------
1     C         10         P         40         S         20
2     C         55         P         60         S         30
3     C         50         P         85         S         10

    然后在应用程序逻辑或业务逻辑（ecc…）中使用约束强制执行type_1, type_2,的值type_3ADD CONSTRAINT type_1 CHECK (type_1 IN ('C', 'P', 'S')

    这种方法的问题在于，每个类型列仍然需要知道哪个是关联的值列（反之亦然）：如果将 value_1 重命名为“val_1”，我们将遇到与使用每个列相同的缺点。

    使用散列结构将值与其类型一起存储

    更好的方法可能是存储cost,price并shipping在一个包含哈希结构的列中：

    hstore(ARRAY['C','10'], ARRAY['P','40'],ARRAY['S','20'])
hstore(ARRAY['C','55'], ARRAY['P','60'],ARRAY['S','30'])
hstore(ARRAY['C','50'], ARRAY['P','85'],ARRAY['S','10'])

    导致

    Items
id    amounts
-----------------------------------
1     "C"=>"10","P"=>"40","S"=>"20"
2     "C"=>"55","P"=>"60","S"=>"30"
3     "C"=>"50","P"=>"85","S"=>"10"

    通过这种方式，我们可以根据需要添加数据类型、命名和重命名它们（在应用程序中或在具有 TYPE、NAME 的另一个表中），并且应用程序可以引发异常来处理任何数据类型的删除。

    这种方法的主要缺点是性能损失。

    • 0

相关问题