库存管理数据库设计 [英] Stock management database design

问题描述

我正在为我的公司创建一个Intranet，我们希望在其中进行库存管理.我们出售和出租警报系统，并且希望对办公室中仍然有哪些产品，出租或出售了什么，在什么时间等有一个很好的了解.

I'm creating an Intranet for my company, and we want to have a stock management in it. We sell and rent alarm systems, and we want to have a good overview of what product is still in our offices, what has been rented or sold, at what time, etc.

此刻，我想到了这种数据库设计:

At the moment I thought about this database design :

每次我们创建新合同时，该合同都是关于物品的位置或销售的.因此，我们有一个Product表(这是产品的类型:警报器，警报手表等)，还有一个Item表，这是项目本身，具有唯一的序列号.我考虑过要这样做，因为我需要跟踪特定物品的位置，是否在客户处(出租的)，是否已出售等.产品与特定的供应商有关，我们可以与之联系接受命令.但是在这里，我有一个问题，订单表不应该与产品相关吗?

Everytime we create a new contract, this contract is about a location or a sale of an item. So we have an Product table (which is the type of product : alarms, alarm watches, etc.), and an Item table, which is the item itself, with it unique serial number. I thought about doing this, because I'll need to have a trace of where a specific item is, if it's at a client house (rented), if it's sold, etc. Products are related to a specific supplier, to whom we can take orders. But here, I have a problem, shouldn't the order table be related to Product ?

这里主要要关注的是库存，物料，运动库存之间的联系.我想创建一个设计，以便能够看到何时从我们的库存中取出某个特定项目，以及何时将其与日期一起输入到库存中.这就是为什么我想到了Movement_stock表. Type_Movement是输入/输出. 但是我在这里有点迷失了，我真的不知道该怎么做.这就是为什么我需要一些帮助.

The main concern here is the link between Stock, Item, Movement stock. I wanted to create a design where I'd be able to see when a specific Item is pulled out of our stock, and when it enters the stock with the date. That's why I thought about a Movement_stock table. The Type_Movement is either In / Out. But I'm a bit lost here, I really don't know how to do it nicely. That's why I'm asking for a bit of help.

推荐答案

我也有同样的需求，这就是我解决您的库存变动问题(这也成为我的问题)的方式.

I have the same need, and here is how I tackled your stock movement issue (which became my issue too).

为了模拟库存变动(+/-)，我有我的supplying和order表.供应充当我的+库存，我订购我的-库存.

In order to modelize stock movement (+/-), I have my supplying and my order tables. Supplying act as my +stock, and my orders my -stock.

如果我们停止这样做，我们可以计算出实际存货，该存货将被转录到此SQL查询中:

If we stop to this, we could compute our actual stock which would be transcribed into this SQL query:

SELECT
    id,
    name,
    sup.length - ord.length AS 'stock'
FROM
    product
# Computes the number of items arrived
INNER JOIN (
    SELECT
        productId,
        SUM(quantity) AS 'length'
    FROM
        supplying
    WHERE
        arrived IS TRUE
    GROUP BY
        productId
) AS sup ON sup.productId = product.id
# Computes the number of order
INNER JOIN (
    SELECT
        productId,
        SUM(quantity) AS 'length'
    FROM
        product_order
    GROUP BY
        productId
) AS ord ON ord.productId = product.id

哪个会给出类似的内容:

Which would give something like:

id  name            stock
=========================
 1  ASUS Vivobook       3
 2  HP Spectre         10
 3  ASUS Zenbook        0
    ...

虽然这可以为您节省一张表，但您将无法对其进行扩展，因此，大多数建模(imho)使用中间的stock表这一事实，主要是出于性能方面的考虑.

While this could save you one table, you will not be able to scale with it, hence the fact that most of the modelization (imho) use an intermediate stock table, mostly for performance concerns.

缺点之一是数据重复，因为您将需要重新运行上面的查询以更新库存(请参见updatedAt列).

One of the downside is the data duplication, because you will need to rerun the query above to update your stock (see the updatedAt column).

好的方面是客户表现.您将通过API提供更快的响应.

The good side is client performance. You will deliver faster responses through your API.

我认为，如果您要管理高流量商店，则还有另一个弊端.您可以想象创建另一个表来存储要重新计算库存的事实，并让用户等到重新计算完成(推送请求或长时间轮询)后，才能检查他/她的每个项目是否仍然可用(库存) > =用户需求).但这是另一笔交易...

I think another downside could be if you are managing high traffic store. You could imagine creating another table that stores the fact that a stock is being recomputed, and make the user wait until the recomputation is finished (push request or long polling) in order to check if every of his/her items are still available (stock >= user demand). But that is another deal...

无论如何，即使库存重新计算查询使用匿名子查询，在大多数相对中等的商店中，它实际上也应该足够快.

Anyway even if the stock recomputation query is using anonymous subqueries, it should actually be quite fast enough in most of the relatively medium stores.

注意

您在product_order中看到，我复制了价格和增值税.这是出于可靠性方面的考虑:在购买时冻结价格，并能够用很多小数位重新计算总数(不会损失任何美分).

You see in the product_order, I duplicated the price and the vat. This is for reliability reasons: to freeze the price at the moment of the purchase, and to be able to recompute the total with a lot of decimals (without loosing cents in the way).

希望它可以帮助路过的人.

Hope it helps someone passing by.

修改

在实践中，我将它与 Laravel 一起使用，而我使用的是

In practice, I use it with Laravel, and I use a console command, which will compute my product stock in batch (I also use an optional parameter to compute only for a certain product id), so my stock is always correct (relative to the query above), and I never manually update the stock table.

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