帮助设计销售数据库设计 [英] Help with design of a sales database design

问题描述

免责声明：我是数据库领域的初学者。



我正在尝试在PostgresQL中设计一个数据库，该数据库将跟踪库存并记录销售情况和购买物品。我打算使用这个数据库作为使用MVVM方法的EF和WPF模型。



我有一个表记录每个项目的当前库存。我的问题是每次销售或购买原材料时都要调整库存。



我尝试了什么：



我想到了解决这个问题的三种基本方法。



第一个是触发器：每次都是在销售表或购买表中插入一条记录，相应调整库存。



第二个是将所有销售额加起来然后加起来我们所有的购买然后找出它们之间的区别。这就产生了一个问题 - 如果我们删除较旧的购买记录或销售记录，计算出的库存将是错误的。



第三个想法是封装每个条目的输入通过存储过程在销售或购买列中插入数据。



您能告诉我哪种方法最好吗？

Disclaimer: I am a beginner in the field of database.

I am trying to design a database in PostgresQL which will track the stock along with recording the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM approach.

I have a table that records the current stock for each item. My problem is to adjust that stock every time a sale occurs or when we purchase the raw materials.

What I have tried:

I have thought of three basic ways to solve the problem.

The first one is trigger: every time a record is inserted in sales table or purchases table, the stock is adjusted accordingly.

The second one is to add up all the sales and then add up all our purchases and then find the difference between them. This poses a problem - if we delete older purchase records or sales records, the calculated stock would be wrong.

The third idea was to encapsulate the entry of each and every insertion of data in sales or purchases column through a stored procedure.

Could you please tell me which approach is the best?

推荐答案

使用第三个表，一个onHand表。



当你购买物品时，你将一个记录添加到库存表并添加购买量到商品的onHand表格。

当你卖掉你在购买表中添加记录的商品时，从onHand表中扣除你卖的商品的金额。

use a third table, a onHand table.

when you buy the item you add a record to the inventory table and add the amount bought to the onHand table for the item.
when you sell the item you add a record to the purchase table and deduct the amount from the onHand table for the item you sold.

这篇关于帮助设计销售数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！