咨询库存数据库设计 [英] Consulting about inventory database design

问题描述

I have a question about inventory project database design

Instead of creating a [ProductBalance] column in products table, I left the balance undefined and created another table [Log]
Log_ID || Product_ID || OperationType_ID   || Input || Output
1            29           1 (Purchases)         5        0
2            24           4 (Sales)             0        2
3            24           5 (SaleReturn)        1        0

* OperationType_ID is related to another table[OperationTypes]

Now when user select some product the query will SUM(Input) - SUM(Output) for this product to get the current balance.

我尝试过：

What I have tried:

This worked well till now, but i want to know is this design is bad?
For example when i wanted to get total sales and saleReturn for products i tried this:
SELECT SUM(A.Output) AS Sales, SUM(B.Input) AS SaleReturn
FROM
(SELECT * from Log WHERE Log.OperationType_ID = 2) A
FULL JOIN
(SELECT * FROM Log WHERE Log.OperationType_ID = 5) B ON A.Stock_ID = B.Stock_ID

推荐答案

首先，您在内容部分中给出的定义与您在查询中提供的条件

First thing is that the definition you give in the "content" section does not match the condition you give in the query

OperationTypeID = 4 (Sales)

SUM(A.Output) AS Sales = (SELECT * from Log WHERE Log.OperationType_ID = 2)

第二件事是你的查询似乎很罗嗦。你可以用更简单的东西替换它

Second thing was that your query seems quite wordy. You could replace that with something a lot simpler

SELECT Sales = Sum(Output), SalesReturn = Sum(Input)
FROM @log
WHERE OperationTypeID IN (4, 5)

至于日志表设计，我会为LogDateTime& TransactionID的。我也厌恶列名，无论是关键词还是保留词;注意输出在查询中是蓝色的，所以我可能会重命名为InventoryRemoved，然后将输入列更改为类似名称，如InventoryAdded

As for the log table design, I would have additional columns for items such as LogDateTime & TransactionID. I also have an aversion to column names that are either keywords or reserved words; notice that Output is blue in the query, so I would probably rename to something like InventoryRemoved and then change the input column to be similarly named like InventoryAdded

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