设计表使得每个项目仅具有一个“当前”项目。股票价值

问题描述

我有一个数据库，其中一个表记录了物品库存。在购买物品时，必要的金额在销售时添加到库存中，扣除必要的金额。



表中的列如下：

I have a Database where one table records the stock of items. On purchase of items, the requisite amount is added to stock while on sale, requisite amount is deducted.

The columns of the table is as follows:

Item(F.key)----Qty----Date----IsCurrent

item属性是items表的外键。数量显示给定日期的库存。



IsCurrent是一个布尔值，表示给定的库存是否是最新库存。



因此，在任何给定时间，对于任何特定项目，只有一条记录，IsCurrent设置为true。



我尝试了什么：



我用了一个索引：

The item attribute is a foreign key to the items table. Quantity shows the stock at the given date.

IsCurrent is a boolean that indicates whether the given stock is the most current one.

So, at any given time, for any particular item, there can be only one record with IsCurrent set to true.

What I have tried:

I have used an index:

CREATE UNIQUE INDEX onlyonecurrent_index
    ON mycompany.stock USING btree
    (item COLLATE pg_catalog."default")
    TABLESPACE pg_default    WHERE iscurrent
;

这样可以解决约束问题。但是，这意味着将记录插入此表是一件麻烦事。我必须找到当前的股票，清除IsCurrent标志，然后输入新的条目。



我想到了两种方法来纠正这个问题。我可以维护两个表，一个是StockHistory，另一个是CurrentStock，存档的数据将放在StockHistory中。



或者，我可以简单地删除IsCurrent标志并对其进行排序按日期列出项目以获得最新条目。



但我不喜欢任何这些方法。



有没有经过试验和测试的方法来处理这样的问题？

This takes care of the constraint. However, it means inserting records to this table is a hassle. I have to find the current stock, clear the IsCurrent flag and then enter the new entry.

I have thought of two ways to correct the problem. I can maintain two tables, one StockHistory and another CurrentStock, where the archived data will be put in StockHistory.

Or, I can simply remove the IsCurrent flag and sort the items by date to get the most current entry.

However I am not fond of any of those approaches.

Is there any tried and tested way to handle a problem like this?

推荐答案

这篇关于设计表使得每个项目仅具有一个“当前”项目。股票价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！