Data model and querying inventory status with stock movement support-CodePudding

I am developing an inventory/warehouse stock tracking system where stocks' quantities can be deducted (sold or used) or moved to another warehouse/location.

Here are my current tables (simplified):

CREATE TABLE stocks
(
    id text /* id of the stock */
    quantity int /* original quantity when the stock is first received */
    location_id int /* where stock is physically located at */
    attributes jsonb /* properties of the stock like size, color, length, etc. */
    created_at datetime /* self-explanatory */
);

CREATE TABLE transfers
(
    id text /* transfer id */
    stock_id text /* id of the stock to be transferred*/
    transfer_quantity int /* how much of the original stock quantity should be transferred */
    to_location_id int /* can be null, if null = used or sold, if not null = transferred to another location */
    created_at datetime /* self-explanatory */
);

/*Dummy Data*/
/*stocks*/
INSERT INTO stocks(id, quantity, location_id) VALUES ('id1', 10, 1);
INSERT INTO stocks(id, quantity, location_id) VALUES ('id2', 10, 2);

/*transfers, (used or sold quantities from specific stocks) */
INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id1', 1);
INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id1', 1);

INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id2', 1);
INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id2', 2);

/*transfer some quantities of a certain stock to another location inside the warehouse */
INSERT INTO transfers(stock_id, transfer_quantity, to_location_id) VALUES ('id2', 1, 1);

I am following a transactional approach/strategy to query the stocks where I subtract the sum of the transfer quantities from the original quantity from the stocks table like below:

SELECT stocks.id,
       stocks.location_id,
       stocks.quantity,
       sum(transfers.transfer_quantity) AS transferred_quantity_total,
       (stocks.quantity - coalesce(sum(transfers.transfer_quantity), 0)) AS remaining_qty
FROM stocks
LEFT JOIN transfers ON transfers.stock_id = stocks.id 
WHERE transfers.to_location_id IS NULL
GROUP BY stocks.id,
         stocks.quantity,
         stocks.location_id
HAVING (stocks.quantity - coalesce(sum(transfers.transfer_quantity), 0)) > 0 /* to only get the stocks on hand */
ORDER BY stocks.id;

Problems, questions, concerns.

This query works well for subtracting the used or sold stocks and seeing the stocks on hand but I am not sure how should I query the data to also subtract and see the moved stocks (which will have non-null 'to_location_id' columns) with their new locations. How can I modify the query to get the desired outcome like below?

Desired result (4 qty of the stock with 'id2' is transferred, 3 out of those 4 transfer transactions are used or sold, 1 of those transfers relates to a stock movement to a new location, so in the end I have 6 qty remaining in location 2 and 1 qty remaining in location 1)

My data model-design may not be the optimal model for my requirements, if so, how can I model my data to reach the desired outcomes? (To be able to subtract sold, used or moved stock quantities and seeing the remaining quantities moved quantities with their current locations). I also need to be able to query locations (including new locations if a stock is moved) with a WHERE clause.
I think if I can find a way to eliminate the original quantity column from the 'stocks' table I could achieve a ledger-like data model where everything related to quantities is stored in a single table. But again, I am not exactly sure how to design this ledger-like transaction table to fulfill my use-cases (aforementioned queries).

Here is a dbfiddle instance with the relevant query and data: https://dbfiddle.uk/GgU4hLRz

I am eager to hear your opinions. Thanks in advance!

CodePudding user response：

This question will be probably closed because it is likely to be answered by opinions. However let me suggest that the usual approach adopted by all the WMSs I know of is, like you yourself hinted, ledger based.

Simply record every incoming or outgoing quantity in a different row - i.e., a transfer will be represented by two records, a negative quantity at the "from" location, and a positive one at the "to" location. This way the available quantity at a location, or in the whole warehouse, is just the algebric sum of all the entries.