0

Long time reader but first post!

I am designing a database that requires me to be able to calculate the inventory available for a perishable good on any given day. In my case, it is for a farm. Farmers can add inventory updates for any present or future date (today I have 4 available, tomorrow I have 5 more available). Farmers also set how long their inventory lasts before it expires (this is per farm, not per item). Order items inventory purchased (order items) must be a part of an order that has a fulfillment date before the inventory expires. The inventory cannot be purchased for a fulfillment date after its expiration. More inventory cannot be purchased than is available.

Currently, I have a job that runs every night that removes expired inventory (inserts it into inventory_expired), by calculating the total amount of inventory that expires today or previously and subtracts the quantity in orders placed and the inventory that was already expired. I feel like this isn't the right way to go about it. My approach lets me accurately calculate the inventory up until the current day, and in the past accurately, but can't calculate future inventory properly, because the future date doesn't know what inventory has expired.

Maybe I am making this too complicated. Is there a better way to structure what I am trying to do? Or is there a function I can write that would be able to calculate the available inventory from my current design?

Here is what is relevant out of my current tables:


CREATE TABLE organizations (
  id int NOT NULL,
  PRIMARY KEY(id)
);
CREATE TABLE farms (
  id int NOT NULL,
  organization_id int NOT NULL,
  inventory_expiration_days int NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (organization_id) REFERENCES organizations (id)
);


CREATE TABLE inventory (
 id int NOT NULL,
 organization_id int NOT NULL,
 farm_id int NOT NULL,
  primary key(id),
 FOREIGN KEY (organization_id) REFERENCES organizations (id),
 FOREIGN KEY (farm_id) REFERENCES farms (id)
);

CREATE TABLE inventory_updates (
  id int NOT NULL,
  date date NOT NULL,
  inventory_id int NOT NULL,
  quantity_added int,
  quantity_removed int,
  FOREIGN KEY(inventory_id) REFERENCES inventory (id)
);

CREATE TABLE inventory_expired (
  id int NOT NULL,
  inventory_id int NOT NULL,
  date date NOT NULL,
  quantity_expired int NOT NULL,
  FOREIGN KEY(inventory_id) REFERENCES inventory (id)
);

CREATE TABLE orders (
  id int NOT NULL,
  fulfillment_date date NOT NULL,
  seller_org_id int NOT NULL,
  primary key(id),
  FOREIGN KEY(seller_org_id) REFERENCES organizations (id)
);

CREATE TABLE order_item (
 id int NOT NULL,
  order_id int not null,
 inventory_id int NOT NULL,
 quantity int NOT NULL,
 FOREIGN KEY(inventory_id) REFERENCES inventory (id),
  FOREIGN KEY(order_id) REFERENCES orders(id)
);

And here is my current function, which works for calculating inventory for dates up to today but not in the future. I put a hard coded date in (2021-02-21) for now.

SELECT t.inventory_id, 
COALESCE(t.total_quantity, 0) as total_quantity, 
COALESCE(exp.quantity_expired_previously, 0) quantity_previously_expired, 
COALESCE(sold.quantity_sold, 0) as quantity_sold,
COALESCE(t.total_quantity, 0) - COALESCE(exp.quantity_expired_previously, 0) - COALESCE(sold.quantity_sold, 0) as quantity_available
FROM
(SELECT u2.inventory_id as inventory_id, SUM(COALESCE(u2.quantity_added, 0) - coalesce(u2.quantity_removed, 0)) as total_quantity
  FROM inventory_updates u2 
  LEFT JOIN inventory i2 on i2.id = u2.inventory_id
  LEFT JOIN farms f2 on f2.organization_id = i2.organization_id
  WHERE u2.date <= ('2021-02-21'::date)
  GROUP BY u2.inventory_id) t
LEFT JOIN (
  SELECT e.inventory_id, COALESCE(SUM(e.quantity_expired), 0) as quantity_expired_previously
  FROM inventory_expired e
  WHERE e.date <= ('2021-02-21'::date)
  GROUP BY e.inventory_id
) exp on exp.inventory_id = t.inventory_id
LEFT JOIN (
  SELECT i.inventory_id, SUM(i.quantity) as quantity_sold
  FROM order_item i
  LEFT JOIN orders o on o.id = i.order_id
  LEFT JOIN farms f on f.organization_id = o.seller_org_id
  WHERE o.fulfillment_date < ('2021-02-21'::date + (f.inventory_expiration_days * interval '1 day'))
  GROUP BY i.inventory_id
) sold on sold.inventory_id = t.inventory_id

I was wondering if some sort of union on all of the updates that occur to the inventory quantities could work better, but also think I am going to run into problems when I have lots of rows, and any sort of sum over everything is going to take a long time. Basically, I am pretty stuck.

2
  • 2
    Great question! Please make sure your example DDL works when copy-pasted into a freshly-created DB - it is currently missing semicolons and commas, a primary key for the inventory and orders tables, an order_id column for the order_item table, and has the incorrect data type ("id" instead if "int") for seller_org_id. – AdamKG Jan 27 at 19:27
  • Oops - thank you! I will check for that next time! – Hana Jan 27 at 20:57
0

I'm ignoring efficiency concerns for the moment, because I'm not convinced it's a problem. Try this route and see if it's too slow.

Use the FILTER clause on aggregate functions to only include those rows that should be valid as-of the date in question. Here's an example that only takes quantity_added and the expiration time into account:

-- Set up 1 farm with a 10-day expiration
insert into organizations select 1;
insert into farms select 2, 1, 10;
insert into inventory select 3, 1, 2;
-- +100 inventory on feb 1, +50 on feb 2
insert into inventory_updates select 4, '2021-02-01', 3, 100, 0;
insert into inventory_updates select 4, '2021-02-02', 3, 50, 0;
with date_range as (
  select d from generate_series('2021-02-01'::date, '2021-02-15', interval '1 days') d
  )
  select d, inventory_id,
  sum(quantity_added) FILTER (
    where inv_upd.date <= d and inv_upd.date > (
      d- (interval '1 days' * f.inventory_expiration_days)
    )) as running_total
from date_range
cross join inventory_updates inv_upd
join inventory on inv_upd.inventory_id=inventory.id
join farms f on f.id=inventory.farm_id
      group by 1, 2
      order by 1, 2;

Which produces:

| d                        | inventory_id | running_total |
| ------------------------ | ------------ | ------------- |
| 2021-02-01T00:00:00.000Z | 3            | 100           |
| 2021-02-02T00:00:00.000Z | 3            | 150           |
| 2021-02-03T00:00:00.000Z | 3            | 150           |
| 2021-02-04T00:00:00.000Z | 3            | 150           |
| 2021-02-05T00:00:00.000Z | 3            | 150           |
| 2021-02-06T00:00:00.000Z | 3            | 150           |
| 2021-02-07T00:00:00.000Z | 3            | 150           |
| 2021-02-08T00:00:00.000Z | 3            | 150           |
| 2021-02-09T00:00:00.000Z | 3            | 150           |
| 2021-02-10T00:00:00.000Z | 3            | 150           |
| 2021-02-11T00:00:00.000Z | 3            | 50            |
| 2021-02-12T00:00:00.000Z | 3            |               |
| 2021-02-13T00:00:00.000Z | 3            |               |
| 2021-02-14T00:00:00.000Z | 3            |               |
| 2021-02-15T00:00:00.000Z | 3            |               |

To finish this, you'd need to also join the orders table and subtract out those, as well as using something like sum(quantity_added - quantity_removed) FILTER (...) for the running_total expression. And, of course, you'd just interpolate your date, the date_range thing is just to illustrate the running-total behavior.

1
  • Thank you for your help here! I was hoping something like this would work, but the problem is that the inventory is rolling. Say I want to know the inventory available today, and I know inventory expires in 7 days. I take all the inventory updates that were added in the last 7 days. But what about the orders? If an order was placed 6 days ago, does that quantity come out of the updates in our current window, or previously, if that makes sense? (Or am I over thinking it?) Here is my next attempt: [link] (db-fiddle.com/f/dWBaZUDJ8QUHknRyQ4Bhhb/0) – Hana Jan 27 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.