Inventory Management System Database Design: ER Diagram, Tables and SQL

Priya Nair runs a hardware shop, nails, screws, hammers, pipes, wire, switches, paint and cement, from a spreadsheet with one row per product and a column where she types how many she has. After a delivery she adds, after a bill she subtracts, and her helper does the same when she is out. Last Saturday the number went wrong three ways in one day, and that evening the sheet said only the measuring tapes needed ordering. This post designs the shop's database from a conversation with her: eleven tables and two views on SQL Server and PostgreSQL, a stock figure that is never typed because it is the sum of everything that came in and went out, a sale the database refuses when the shelf cannot cover it, and Saturday's drift reproduced number by number.

The conversation

Priya: Hammers, taps and switches go by the piece, nails by the kilo, pipe and wire by the metre. Cement is a 50 kg bag, so a bag is a piece. Each product has a number I watch: when cement falls to 20 bags or below, I call a supplier. Cement comes from Weber Building Supply or Silva Cement Depot, and they quote different prices. Nails come from Mensah Fasteners or Weber.

Designer: So every product has a unit, some quantities have decimals, every product has a reorder level, and a product has several suppliers and a supplier several products, with the quoted price belonging to the pair. What happens when goods arrive?

Priya: The supplier brings an invoice with its own number. It lists each product, how many, and the price per unit. The price moves: Weber charged 380 a bag on the 2nd and 395 on the 9th. Two suppliers can both send me an invoice with the same number, but not the same supplier twice.

Designer: A purchase belongs to one supplier and has lines, each keeping the price you actually paid, and the invoice number is unique per supplier. Who buys at your counter?

Priya: Mostly walk-in people whose names I never write down. Contractors like David Chen have an account, so for them I keep a name and a phone. A bill has lines, just like a delivery, and the price on the bill is not always the list price: David got cement at 410 instead of 420 because he took 40 bags.

Designer: So a sale has an optional customer, and the sale line keeps the price charged as the purchase line keeps the price paid. How do you keep the stock number right today?

Priya: I type it. Last Saturday it went wrong three ways. Arjun counted the switches at nine, saw 20, and typed his 20 at noon, after I had sold 10 at eleven and typed 10. I typed 51 bags of cement when it was 15. And Elena Rossi's bill had two lines: I typed the pipe and missed the two taps. Nobody typed the two metres of cracked pipe we threw away either. Breakage happens, and at closing the scale sometimes says less nails than the sheet.

Designer: So the typed number can be wrong and cannot explain itself. Breakage and a recount are adjustments with a date, a quantity and a reason. The stock of a product is then everything that came in minus everything that went out, added up from those records, and nobody types it. Where do we start on day one?

Priya: We count the shelves on the 1st of October and start from there. And if the computer says 15 bags, it should not let me bill 20. To know whether a product makes money, I compare what I sell it for with what I paid the last time.

Designer: The count goes in as an opening adjustment per product. The database refuses a sale line that would take the stock below zero. And gross profit is the price charged minus the last purchase price, times the quantity, per sale line.

From sentences to tables

Stock is a sum, not a column

The first view lines up the three kinds of movement as one list with a sign: purchase lines count up, sale lines count down, adjustments carry their own sign.

CREATE VIEW stock_movements AS
SELECT pi.product_id, p.purchase_date AS movement_date, 'purchase' AS movement_type,
       pi.purchase_id AS document_id, pi.quantity AS quantity
FROM purchase_items AS pi
JOIN purchases AS p ON p.purchase_id = pi.purchase_id
UNION ALL
SELECT si.product_id, s.sale_date, 'sale', si.sale_id, -si.quantity
FROM sale_items AS si
JOIN sales AS s ON s.sale_id = si.sale_id
UNION ALL
SELECT a.product_id, a.adjustment_date, a.reason, a.adjustment_id, a.quantity_change
FROM stock_adjustments AS a;

The second sums it per product, and is the only place in the design that knows how much is on the shelf:

CREATE VIEW stock_on_hand AS
SELECT p.product_id, p.sku, p.product_name, u.unit_code, p.reorder_level,
       CAST(COALESCE(SUM(m.quantity), 0) AS numeric(12,3)) AS on_hand
FROM products AS p
JOIN units AS u ON u.unit_id = p.unit_id
LEFT JOIN stock_movements AS m ON m.product_id = p.product_id
GROUP BY p.product_id, p.sku, p.product_name, u.unit_code, p.reorder_level;

The opening count of 1 October is an adjustment row per product, so the sum starts from the shelves rather than from zero, and a check constraint keeps the signs honest: an opening count is positive, damage is negative, a count correction is anything but zero. Priya's "it should not let me bill 20" is a trigger that reads the view after each sale line is written and throws the line back if the product has gone negative. On PostgreSQL:

CREATE FUNCTION check_stock_after_sale() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
    v_sku      varchar(12);
    v_on_hand  numeric(12,3);
    v_unit     varchar(5);
BEGIN
    SELECT s.sku, s.on_hand, s.unit_code
      INTO v_sku, v_on_hand, v_unit
      FROM stock_on_hand AS s
     WHERE s.product_id = NEW.product_id;
    IF v_on_hand < 0 THEN
        RAISE EXCEPTION 'Sale refused: stock of % would fall to % %', v_sku, v_on_hand, v_unit;
    END IF;
    RETURN NULL;
END;
$$;

SQL Server has the same rule as a statement-level trigger that reads the inserted rows and runs THROW. Section 3 tries to sell 20 bags of cement with 15 on hand, then tries to raise an existing line from 40 bags to 60:

ERROR:  Sale refused: stock of CEM-50 would fall to -5.000 pc
CONTEXT:  PL/pgSQL function check_stock_after_sale() line 12 at RAISE

Both are refused on both engines with the same sentence, and the checks that follow show the cement still at 15.000, the sale still with two lines, and the old line still at 40. A third attempt, a damage adjustment with a positive quantity, is refused by the sign constraint.

The ER diagram

Five questions the shop asks

Stock on hand of every product, straight from the view, in the product's own unit:

    sku    |           product_name            | on_hand | unit_code
-----------+-----------------------------------+---------+-----------
 CEM-50    | Cement bag 50 kg                  |  15.000 | pc
 HAM-500   | Claw hammer 500 g                 |   8.000 | pc
 NAIL-50   | Wire nails 50 mm                  |  33.500 | kg
 PAINT-4L  | White emulsion paint 4 L          |   9.000 | pc
 PVC-20    | PVC pipe 20 mm                    |  98.000 | m
 SCREW-440 | Wood screws 4 x 40 mm, box of 100 |  36.000 | pc
 SW-6A     | Switch 6 A                        |  10.000 | pc
 TAP-15    | Brass tap 15 mm                   |   5.000 | pc
 TAPE-5M   | Measuring tape 5 m                |   4.000 | pc
 WIRE-15   | Copper wire 1.5 sq mm             | 264.500 | m
(10 rows)

Which products are at or below their reorder level, and who sells each cheapest, read from the supplier prices. "At or below" is Priya's own wording, which is why the tap at exactly 5 of 5 is listed:

   sku   | on_hand | reorder_level | unit_code |    cheapest_supplier     | supplier_price
---------+---------+---------------+-----------+--------------------------+----------------
 CEM-50  |  15.000 |        20.000 | pc        | Silva Cement Depot       |         372.00
 SW-6A   |  10.000 |        15.000 | pc        | Zhang Electricals        |          38.00
 TAP-15  |   5.000 |         5.000 | pc        | Haddad Pipes and Wires   |         240.00
 TAPE-5M |   4.000 |         5.000 | pc        | Petrova Paints and Tools |         100.00
(4 rows)

Sales per day for one week, with the closed Sunday kept in the list by generating the days first:

 sale_date  | day_name  | sales_count |  total
------------+-----------+-------------+----------
 2026-10-05 | Monday    |           3 | 10020.00
 2026-10-06 | Tuesday   |           2 |  3861.00
 2026-10-07 | Wednesday |           2 |  2826.00
 2026-10-08 | Thursday  |           2 | 14590.00
 2026-10-09 | Friday    |           1 |  5120.00
 2026-10-10 | Saturday  |           3 | 18614.00
 2026-10-11 | Sunday    |           0 |     0.00
(7 rows)

Purchases per supplier, with the supplier nothing was bought from kept at zero by an outer join:

      supplier_name       | purchases | total_value
--------------------------+-----------+-------------
 Weber Building Supply    |         2 |    29425.00
 Haddad Pipes and Wires   |         1 |     7240.00
 Petrova Paints and Tools |         2 |     6770.00
 Mensah Fasteners         |         2 |     4700.00
 Zhang Electricals        |         1 |      760.00
 Silva Cement Depot       |         0 |        0.00
(6 rows)

And gross profit per product, the price charged minus the last purchase price, times the quantity. The measuring tape was never purchased, only counted on day one, so its last price and its profit are unknown and stay NULL rather than pretend to be zero:

    sku    | qty_sold | revenue  | last_purchase_price | gross_profit
-----------+----------+----------+---------------------+--------------
 CEM-50    |   90.000 | 37400.00 |              395.00 |      1850.00
 PAINT-4L  |    7.000 |  6650.00 |              760.00 |      1330.00
 PVC-20    |   60.000 |  2280.00 |               27.00 |       660.00
 WIRE-15   |   85.500 |  1881.00 |               15.50 |       555.75
 SCREW-440 |   16.000 |  1360.00 |               60.00 |       400.00
 TAP-15    |    5.000 |  1600.00 |              240.00 |       400.00
 SW-6A     |   20.000 |  1100.00 |               38.00 |       340.00
 HAM-500   |    3.000 |  1350.00 |              345.00 |       315.00
 NAIL-50   |    8.750 |  1050.00 |               92.00 |       245.00
 TAPE-5M   |    3.000 |   360.00 |                     |
(10 rows)

One engine difference hides in that last row: in a descending sort PostgreSQL puts NULL first and SQL Server puts it last, so the PostgreSQL query says DESC NULLS LAST, which SQL Server 2022 rejects as a syntax error. Both engines also needed their 2022-era series function for the week of days; on SQL Server it exists only at compatibility level 160.

Saturday, reproduced

Section 5 builds Priya's sheet as products_with_stock, loaded as it stood on Friday night, when it agreed with the movements for all ten products. Then Saturday's six hand edits run as six updates, every one of them accepted, because nothing in a typed number can refuse or explain itself. The reorder question on the sheet that evening:

   sku   | stock | reorder_level
---------+-------+---------------
 TAPE-5M | 4.000 |         5.000
(1 row)

The typed numbers against the sum of the movements, four products drifted, three of them past their reorder level:

  sku   | typed_stock | sum_of_movements | difference
--------+-------------+------------------+------------
 CEM-50 |      51.000 |           15.000 |     36.000
 PVC-20 |     100.000 |           98.000 |      2.000
 SW-6A  |      20.000 |           10.000 |     10.000
 TAP-15 |       7.000 |            5.000 |      2.000
(4 rows)

The 36 is the 51 typed for 15. The 10 is Arjun's nine o'clock count typed at noon over the eleven o'clock sale. The 2 taps are the missed bill line, and the 2 metres of pipe are the cracked length nobody typed. The repaired query on the view finds all four products at or below their level, and a stock card with a running balance explains every unit of pipe, including the damage row the sheet never got:

 movement_date | movement_type | document_id | quantity | balance
---------------+---------------+-------------+----------+---------
 2026-10-01    | opening count |           5 |   60.000 |  60.000
 2026-10-07    | purchase      |           5 |  100.000 | 160.000
 2026-10-07    | sale          |           6 |  -42.000 | 118.000
 2026-10-10    | damage        |          13 |   -2.000 | 116.000
 2026-10-10    | sale          |          11 |  -18.000 |  98.000
(5 rows)

What this design does not do

The trigger checks the stock at the moment a line is written, not as of the sale's date, and it takes no lock, so two tills selling the last bag at the same instant were not tested. Adjustments and deleted purchase lines are not checked against stock. Nothing stops a purchase line for a product that supplier does not list. And the gross profit uses the last purchase price, as Priya asked, not the cost of the units actually sold: all 90 bags of cement are costed at 395 although 40 were bought at 380. A cost-of-goods figure is the next project, not this one.

Frequently asked

What tables does an inventory management system database need?

Eleven in this design: units, categories, products (with a reorder level and no stock column), suppliers, supplier_products (the quoted price per pair), purchases and purchase_items (the price paid), customers, sales and sale_items (the price charged), and stock_adjustments (opening counts, damage, count corrections). Two views, stock_movements and stock_on_hand, turn those rows into the stock figure.

Should stock quantity be stored in the products table?

No. In this post's run a typed stock column drifted on four of ten products in one Saturday: a count typed over a later sale, a 51 typed for 15, a missed bill line and an untyped breakage. The sum of purchase lines, sale lines and adjustments was right for all ten, and a stock card with a running balance could explain every unit. Store the movements and sum them in a view.

How do you stop a sale when there is not enough stock?

With a trigger on the sale lines that reads the stock view after the line is written and raises an error if the product has gone below zero: a PL/pgSQL function with RAISE EXCEPTION on PostgreSQL, an AFTER INSERT, UPDATE trigger with THROW on SQL Server. In this post's run both refused 20 bags of cement against 15 on hand, and an update that raised an old line from 40 to 60, leaving the stock and the lines unchanged.

Next: Part 7, a hotel: room types with seasonal rates, reservations that cannot overlap, a bill that adds nights and services, and the status board that was wrong three times by two in the afternoon.