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.

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

Solution 2

This is an interesting discussion and one that also could be augmented with stock availability as of a certain date... This means storing:

• Planned Orders for the Product on a certain date
• Confirmed Orders as of a certain date
• Orders Delivered
• Orders Returned (especially if this is a hire product)

Each one of these product movements could be from and to a location

The user queries would then include:

• What is my overall stock on hand
• What is due to be delivered on a certain date
• What will the stock on hand be as of a date overall
• What will the stock on hand be as of a date for a location

The inventory design MUST take into account the queries and use cases of the users to determine design and also breaking normalisation rules to provide adequate performance at the right time.

Lots to consider and it all depends on the software use cases.

Comments

[Asif Mushtaq]

why do you need separate stocks table? why not just one more column quantity in the product?

[Anwar]

Good question, I think this is a design decision, and a column in the product table would have done the job as well. Maybe the only argument in favor of a separate table would be to be able to know since when the last time was when the stock of the product has been computed. But if this information is not useful for the product you make, then a column is sufficient enough I guess.