Question

I m trying to make a database for a construction depot (there where are stored materials for construction). I need to know all the materials stored (products in), all the products sold and all the products that had left. I m thinking to do like this:


Products--> Deliver <--Depot (many to many) - here I see all the products in.

Depot--> Sell <--Products (many to many) - here I see what I have sold.

And what is left: To make a difference between "Deliver" and "Sell".

有什么正确之处吗?你还有别的主意吗?谢谢你的帮助。

Answer 1

处理清单跟踪系统的另一个办法是,将清单跟踪系统像“强”双入式会计(/强)系统一样处理。这将需要您将您的 Deliver 和表格合并成一个清单movement 表格。每次移动包括两个记录,一个记录为正数,另一个记录为负数。



以这种方式对待库存系统的优点是,很容易使用汇总查询来获得任何仓库某一时间点的任何产品的库存状况(库存)。



<强度 > EDIT:  表结构...

PRODUCT
-ID (PK)
-Description
-...

DEPOT
-ID (PK)
-Name
-Location
-...

MOVEMENT
-ID (PK)
-DateTime
-ProductID (FK)
-DepotID (FK)
-Quantity /* Positive=Increase, Negative=Decrease */
-OffsettingMovementID (FK) /* Points to the other half of the entry */

友情链接