设计表使得每个项目仅具有一个“当前”项目。股票价值 [英] Design table such that each item has only one "current" stock value

问题描述

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



表中的列如下：

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?

推荐答案

方法A

您可以定义列'IsCurrent将'STOCK'表作为NULLable并在列上定义一个UNIQUE约束。

每当你插入一条记录时，生成2个SQL：

（1）

更新STOCK设置IsCurrent = NULL其中item =< item_key>

（2）

插入STOCK值（< item_key>，< qty> ，< tran_date>，TRUE）



由于UNIQUE约束忽略NULL，这应该可以工作



方法B

或者生成一个联合国每个事务的ique TRANSACTION键（可能是数据库序列），并在ITEM和STOCK表中添加列来存储该值。再次，您将需要生成2个SQL：

（1）

插入STOCK值（< item_key>，< qty>，< tran_date>，< trans_key>）

（2）

更新ITEM集LATEST_TRAN_KEY =< trans_key>其中item_key =< item_key>



不用说你可以识别最新记录'stock.tran_key = item.latest_tran_key'



（当然，您无法为此列定义外键约束ITEM.Latest_Tran_Key，因为您已在STOCK表中为列ITEM定义了外键）

Method A
You can define column 'IsCurrent' of table 'STOCK' as NULLable and define a UNIQUE constraint on the column.
Whenever you insert a record, generate 2 SQLs:
(1)
update STOCK set IsCurrent = NULL where item = <item_key>
(2)
insert into STOCK values (<item_key>, <qty>, <tran_date>, TRUE)

Since UNIQUE constraint ignores NULLs this should work

Method B
Alternatively generate a unique TRANSACTION key (maybe Database Sequence) for every transaction and add columns in both ITEM and STOCK Tables to store this value. Again you will need to generate 2 SQLs:
(1)
insert into STOCK values (<item_key>, <qty>, <tran_date>, <trans_key>)
(2)
update ITEM set LATEST_TRAN_KEY = <trans_key> where item_key = <item_key>

Needless to say you can identify the latest record 'where stock.tran_key = item.latest_tran_key'

(Of course you cannot define a Foreign key constraint for this column ITEM.Latest_Tran_Key as you have already defined a foreign key for column ITEM in STOCK table)

对prev post的更正 - 唯一约束应该是（item，iscurrent）。

correction to prev post - Unique constraint should be on (item, iscurrent).

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