PostgreSQL – Calculate Stock Value

database-designpostgresqlpostgresql-performancetrigger

I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my simplified db schema (also shown below).

Table products has a stock field. This field must be calculated from products' total purchases and sales quantity.

SUM(purchases.quantity) - SUM(sales.quantity)

I'm looking for simple, performant and most important race conditions safe solution. My current (but not final) idea is to use db triggers here. But I have no mind how to implement them the right way.

CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY,
    name varchar,
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    PRIMARY KEY (id)
);

CREATE TABLE purchases (
    id INT GENERATED ALWAYS AS IDENTITY,
    quantity INT NOT NULL DEFAULT 0,
    product_id INT NOT NULL,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE sales (
    id INT GENERATED ALWAYS AS IDENTITY,
    quantity INT NOT NULL DEFAULT 0,
    product_id INT NOT NULL,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);

INSERT INTO products (name)
        VALUES('Super Power');

INSERT INTO purchases (product_id, quantity)
        VALUES(1, 2), (1, 3), (1, 2);

INSERT INTO sales (product_id, quantity)
        VALUES(1, 1), (1, 2), (1, 2);

SELECT
    *
FROM
    products

Best Answer

I'm not really sure if you have much of an issue, as long as you update the products.stock field every time you either INSERT INTO the purchases or sales tables. No need for a Trigger in this scenario. Also you should ensure your update on the products.stock happens in the same Transaction as your INSERT INTO the purchases and sales tables.

Alternatively you could also design your schema to not store the stock field on the products table. Rather create a View that joins products, purchases, and sales together, grouped on the product and aggregates the difference in purchases and sales per product as its stock, that way it's instantly always up to date whenever the purchases or sales change.

Related Question