Stock management database design

Question

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 :

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 ?

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.

Answer 1

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

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

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
    ...

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.

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).

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.

Note

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.

Edit

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.