主页 / dba / 问题 / 176935
Accepted
Gunnar Norred
Asked: 2017-06-22 12:39:46 +0800 CST

我将如何跟踪数据库中的所有价格变化，以便在“y”日期获得“x”产品的价格

  • 772

我需要跟踪产品价格变化，以便我可以在给定日期查询数据库以获取产品价格。该信息用于计算历史审计的系统中，因此它必须根据购买日期返回正确产品的正确价格。

我更喜欢在构建数据库时使用 postgres。

我需要数据库的设计，但也欢迎任何和所有最佳实践建议。

database-design best-practices

3 个回答

  1. Best Answer

    如果我正确理解该场景，您应该定义一个保留价格时间序列的表；因此，我同意，这与您正在使用的数据库的时间方面有很大关系。

    商业规则

    让我们从概念层面开始分析情况。因此，如果在您的业务领域中，

    • 以一对多价格购买产品，
    • 每个购买价格在确切的StartDate变为Current，并且
    • Price EndDate（表示价格不再是Current的日期）等于紧随其后Price的StartDate，

    那么这意味着

    • 价格为当前的不同时期之间没有差距（时间序列是连续的或结合的），并且
    • 价格的结束日期是一个可导出的数据。

    图 1所示的IDEF1X图虽然高度简化，但描述了这样一个场景：

    图 1 - 产品价格简化 IDEF1X 图 - 场景 A

    说明性逻辑布局

    下面的 SQL-DDL 逻辑级设计，基于上述 IDEF1X 图，说明了一种可行的方法，您可以根据自己的确切需求进行调整：

    -- At the physical level, you should define a convenient 
-- indexing strategy based on the data manipulation tendencies
-- so that you can supply an optimal execution speed of the
-- queries declared at the logical level; thus, some testing 
-- sessions with considerable data load should be carried out.

CREATE TABLE Product (
    ProductNumber INT      NOT NULL,
    Etcetera      CHAR(30) NOT NULL,
    --
    CONSTRAINT Product_PK PRIMARY KEY (ProductNumber)
);

CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    Amount        INT  NOT NULL, -- Retains the amount in cents, but there are other options regarding the type of use.
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT AmountIsValid_CK    CHECK       (Amount >= 0)
);

    该Price表有一个由两列组成的复合 PRIMARY KEY，即ProductNumber（受约束，反过来，作为引用的 FOREIGN KEY Product.ProductNumber）和StartDate（指出以特定价格购买特定产品的特定日期） .

    如果产品在同一天以不同的价格购买，而不是列，您可以包括一个标记为当以确切的价格购买给定产品时保持即时的标签。然后必须将 PRIMARY KEY 声明为。StartDateStartDateTime(ProductNumber, StartDateTime)

    如图所示，上述表是一张普通的表，因为您可以声明 SELECT、INSERT、UPDATE 和 DELETE 操作来直接操作其数据，因此它 (a) 允许避免安装额外的组件 (b) 可以在所有必要时进行一些调整的主要 SQL 平台。

    数据操作示例

    为了举例说明一些看起来很有用的操作，假设您已分别在Product和Price表中插入了以下数据：

    INSERT INTO Product
    (ProductNumber, Etcetera)
VALUES
    (1750, 'Price time series sample'); 

INSERT INTO Price
    (ProductNumber, StartDate, Amount)
VALUES
    (1750, '20170601', 1000),
    (1750, '20170603', 3000),   
    (1750, '20170605', 4000),
    (1750, '20170607', 3000);

    由于Price.EndDate是可导出的数据点，因此您必须准确地通过可以创建为视图的派生表来获取它，以生成“完整”时间序列，如下所示：

    CREATE VIEW PriceWithEndDate AS

    SELECT  P.ProductNumber,
            P.Etcetera AS ProductEtcetera,
           PR.Amount   AS PriceAmount,
           PR.StartDate,
           (
                SELECT MIN(StartDate)
                      FROM Price InnerPR
                     WHERE P.ProductNumber   = InnerPR.ProductNumber
                       AND InnerPR.StartDate > PR.StartDate
           ) AS EndDate
        FROM Product P
        JOIN Price   PR
          ON P.ProductNumber = PR.ProductNumber;

    然后是直接从该视图中选择的以下操作

      SELECT ProductNumber,
         ProductEtcetera,
         PriceAmount,
         StartDate,
         EndDate
    FROM PriceWithEndDate 
ORDER BY StartDate DESC;

    提供下一个结果集：

    ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         4000  2017-06-07  NULL      -- (*) 
         1750  Price time series…         3000  2017-06-05  2017-06-07
         1750  Price time series…         2000  2017-06-03  2017-06-05
         1750  Price time series…         1000  2017-06-01  2017-06-03

-- (*) A ‘sentinel’ value would be useful to avoid the NULL marks.

    现在，让我们假设您有兴趣获取2017 年 6月2 日主要由1750Price确定的整个数据。看到断言（或行）在从 (i) it到 (ii) its的整个Interval期间是当前的或有效的，那么这个 DML 操作ProductProductNumber Date PriceStartDateEndDate

     SELECT ProductNumber,
        ProductEtcetera,
        PriceAmount,
        StartDate,
        EndDate
   FROM PriceWithEndDate
  WHERE ProductNumber = 1750        -- (1) 
    AND StartDate    <= '20170602'  -- (2)
    AND EndDate      >= '20170602'; -- (3)

-- (1), (2) and (3): You can supply parameters in place of fixed values to make the query more versatile.

    产生以下结果集

    ProductNumber  ProductEtcetera     PriceAmount  StartDate   EndDate
-------------  ------------------  -----------  ----------  ----------
         1750  Price time series…         1000  2017-06-01  2017-06-03

    解决了上述要求。

    如图所示，PriceWithEndDate视图在获取大部分可派生数据方面起着至关重要的作用，并且可以以相当普通的方式从 SELECTed FROM 中获取。

    考虑到您的首选平台是 PostgreSQL，官方文档站点的此内容包含有关“物化”视图的信息，如果该方面出现问题，可以通过物理级别机制帮助优化执行速度。其他 SQL 数据库管理系统 (DBMS) 提供了非常相似的物理工具，尽管可以应用不同的术语，例如Microsoft SQL Server 中的“索引”视图。

    您可以在此 db<>fiddle和此 SQL Fiddle中查看讨论的 DDL 和 DML 代码示例。

    相关资源

    • 在本问答中，我们讨论的业务环境包括产品价格的变化，但范围更广，因此您可能会感兴趣。

    • 这些 Stack Overflow 帖子涵盖了有关在 PostgreSQL中保存货币数据的列的类型的非常相关的点。

    对评论的回应

    这看起来与我所做的工作相似，但我发现使用价格（在本例中）具有 startdate 列和 enddate 列的表更方便/高效 - 所以你只是在寻找具有 targetdate 的行>= 开始日期和目标日期 <= 结束日期。当然，如果数据没有与这些字段一起存储（包括 9999 年 12 月 31 日的结束日期，而不是 Null，其中不存在实际的结束日期），那么您必须做一些工作来生成它。我实际上让它每天运行，默认情况下结束日期 = 今天的日期。另外，我的描述要求 enddate 1 = startdate 2 减去 1 天。—— @罗伯特·卡内基，2017-06-22 20:56:01Z

    我在上面提出的方法解决了前面描述的特征的业务域，因此应用您关于将EndDate命名的基表声明为列（不同于“字段”）的Price建议意味着数据库的逻辑结构将不能正确反映概念图式，必须精确定义和反映概念图式，包括区分 (1)基础信息和 (2)可导出信息。

    除此之外，这样的做法会引入重复，因为EndDate可以通过 (a) 派生表以及 (b) 名为 的基表Price以及因此重复的EndDate列来获得 。虽然这是一种可能性，但如果从业者决定采用上述方法，他或她应该明确地警告数据库用户它所涉及的不便和效率低下。这些不便和低效率之一是，例如，迫切需要开发一种机制，以确保在任何时候，每个Price.EndDate值都等于手头值Price.StartDate的紧接连续行的列的Price.ProductNumber值。

    相比之下，老实说，我提出的生成相关派生数据的工作一点也不特别，并且需要 (i) 保证数据库抽象的逻辑和概念级别之间的正确对应，以及 (ii ) 确保数据完整性，如前所述，这两个方面都非常重要。

    如果您谈论的效率方面与某些数据操作操作的执行速度有关，那么它必须在适当的地方进行管理，即在物理级别，例如通过有利的索引策略，基于 (1 ) 特定的查询趋势和 (2) 使用的 DBMS 提供的特定物理机制。否则，牺牲适当的概念逻辑映射和损害所涉及数据的完整性很容易将一个健壮的系统（即，有价值的组织资产）变成不可靠的资源。

    不连续或不相交的时间序列

    另一方面，在某些情况下，保留EndDate时间序列表中的每一行不仅更方便、更高效，而且是必需的，尽管这当然完全取决于特定于业务环境的要求。这种情况的一个例子出现在

    • StartDate和EndDate信息都在每个 INSERTion 之前保存（并通过它保留），并且
    • 在价格为当前的不同时期的中间可能存在缺口（即，时间序列是不连续的或不连续的）。

    我已经在图 2中显示的 IDEF1X 图中表示了上述场景。

    图 2 - 产品价格简化 IDEF1X 图 - 场景 B

    在这种情况下，是的，假设Price表必须以类似于以下的方式声明：

    CREATE TABLE Price (
    ProductNumber INT  NOT NULL,
    StartDate     DATE NOT NULL,
    EndDate       DATE NOT NULL,
    Amount        INT  NOT NULL,
    --
    CONSTRAINT Price_PK            PRIMARY KEY (ProductNumber, StartDate, EndDate),
    CONSTRAINT Price_to_Product_FK FOREIGN KEY (ProductNumber)
        REFERENCES Product (ProductNumber),
    CONSTRAINT DatesOrder_CK       CHECK       (EndDate >= StartDate)
);

    而且，是的，逻辑 DDL 设计简化了物理级别的管理，因为您可以在相对更简单的配置中建立包含EndDate列（如图所示，在基表中声明）的索引策略。

    然后，像下面这样的 SELECT 操作

     SELECT  P.ProductNumber,
         P.Etcetera,
        PR.Amount,
        PR.StartDate,
        PR.EndDate
   FROM Price   PR
   JOIN Product P
  WHERE P.ProductNumber = 1750       
    AND StartDate      <= '20170602'  
    AND EndDate        >= '20170602';

    可用于推导出2017 年 6月2 日主要由1750Price确定的全部数据。ProductProductNumber Date

    • 13

  2. 我相信你会想看看Temporal Tables。这些提供的功能可以完全满足您的需求，并且可以在 Postgres 中使用适当的扩展名。

    这个概念看起来也与 DB 无关，因为它在各种 RDBMS 平台上提供。

    • 4

  3. 我在这里给出了一个相对简单的答案，不需要对数据库进行特殊扩展（因此可以与任何数据库一起使用）。

    • 1

相关问题