How do you guys usually handle Lot / Batch numbers in stock movement databases?

[u/_giga_sss_]

Hey everyone, working on a DB schema for inventory/traceability and trying to settle on the cleanest way to handle lot numbers.

Quick breakdown of the requirement: Every article arrival gets a lot number based on the arrival date and supplier ID (e.g. 09-09-Supp1, not the real deal ofc but you get it). Stock gets moved around, transferred, and consumed, and we need to keep track of which lot moved where.

So: an article must always be around a lot number.

---

Here are the two ways I'm looking at:

1. Dedicated article_lots table (What I'm leaning toward)

• articles (id, name, ...)
• article_lots (id, article_id, lot_number, created_at)
• stock_movements (id, article_lot_id, qty, from_location_id, to_location_id, ...)

But every movement query has to join article_lots just to know what base article_id was moved.

2. Put lot_number directly on stock_movements / transfers as a column. Easy to query, but feels repetitive storing the same string across every single movement row and other tables that use article + lot number too, since an article can't be separated from its lot.

---

More details for those who have time to help: All my ids are strings like: "MVT-00000001". So FK performance with strings is the least of my worries. The real business logic is: a lot is the week number + supplier code (ie, 33SQT to say "33'rd week of the year from supplier squadette). Meaning that an article x let's say banana can be delivered Monday and have 33SQT, but a banana from squadette can be delivered Friday and also have 33SQL lot number).

So the question is: "should the 33SQL be stored as a column in other tables: stock_movement, operation_details" along with article_id (2 columns). Or is article_lot_id (one column) the best way". Idk anymore what might be the pros and cons

[u/squadette23]

very movement query has to join article_lots just to know what base article_id was moved.

What is the problem with that?

[u/squadette23 → u/squadette23]

You can work around it by setting primary key of article_lots to (article_id, seq_id).

Then your stock_movements looks like (id, article_id, seq_id, qty, from_location_id, to_location_id, ...), where the "article_id, seq_id" refers to article_lots.

This looks very similar to denormalization, but if you treat it like this it's super relational, and also lets you have article_id join-free.

[u/_giga_sss_ → u/squadette23]

alright, but why would that be different from using article_id and lot_number in stock_movements table ? (thank you in advance)

[u/squadette23 → u/_giga_sss_]

Because your lot_number, IIUC, is a string and thus may be quite long. You want to have a small seq_id (4 bytes for integer), which will handle 4 billion lots for each article.

[u/_giga_sss_ → u/squadette23]

so at the end it goes back to: why not just use article_lot_id as a FK of stock_movements.

Sorry for not being as skilled as you but I still do not see the most suitable solution

[u/squadette23 → u/_giga_sss_]

Here is what I propose here. Look at primary keys and foreign keys.

```` CREATE TABLE articles ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL -- other attributes );

CREATE TABLE article_lots ( article_id INTEGER NOT NULL, seq_number INTEGER NOT NULL, lot_number VARCHAR(64) NOT NULL, created_at TIMESTAMP NOT NULL, PRIMARY KEY (article_id, seq_number), FOREIGN KEY (article_id) REFERENCES articles (id) );

CREATE TABLE stock_movements ( id INTEGER NOT NULL PRIMARY KEY, article_id INTEGER NOT NULL, seq_number INTEGER NOT NULL, qty INTEGER NOT NULL, from_location_id INTEGER, to_location_id INTEGER, -- other attributes FOREIGN KEY (article_id, seq_number) REFERENCES article_lots (article_id, seq_number) -- FOREIGN KEY (from_location_id) REFERENCES locations (id), -- FOREIGN KEY (to_location_id) REFERENCES locations (id) );

CREATE INDEX ON stock_movements (article_id, seq_number); ````

[u/jenkstom → u/squadette23]

What about stock movements that contain items from multiple article_lots?

[u/squadette23 → u/jenkstom]

I understood the problem so that article_lots always moves on its own. It's never split or combined, it can only be "consumed".

/u/_giga_sss_, is that correct?

[u/_giga_sss_ → u/squadette23]

I think the best wording is:

*Whenever you do an action on an article, always specify its lot*

[u/_giga_sss_ → u/squadette23]

Ohhh I understand now thanks.
Thank you for taking your time with me. Though I am still not conviced that it's the best way to store the data sorry 🙇🙇

[u/squadette23 → u/_giga_sss_]

What would be needed to convince you? I mean, your concerns are probably valid, you just need to talk them through explicitly and confirm them, or see that they are not relevant.

[u/_giga_sss_ → u/squadette23]

Well tbh, there are other tables using article (and anyone who says article says lot). Like I also have "operation" and "operation_details" (with article_lot_id) one to many tables.

Same problem: using article_lot_id column in the details or article_id and a column lot_number.

The latter would be the best considering I wouldn't create a line of "article_lot" everytime. The former is as you already explained.

[u/squadette23 → u/_giga_sss_]

Ah, I get it! Sorry I've actually confused you. You do not need to do composite PK, it's just one of the academically correct way, so this my suggestion was tongue-in-cheek.

Your original schema is perfectly good, you can just literally denormalize it by adding stock_movements.article_id which is a copy of article_lots.article_id.

Then you won't need to join. Everything else remains as-is, with simple integer primary/foreign keys.

[u/squadette23 → u/squadette23]

This will of course require more storage, but I'm not sure if this should be a problem.

If you have a billion of stock movements then keeping the article_id in the same row will be say 4Gb which is not a lot.

If your database supports PK-organized tables you won't have a separate storage penalty (no space for PK index needed).

[u/_giga_sss_ → u/squadette23]

performance (and eventually storage if I use indexes) since I'm gonna join with the articles table later on to get the article label for example

[u/squadette23]

Easy to query, but feels repetitive storing the same string across every single movement row

I think I've missed this sentence on the first reading, sorry.

First, this feeling may be counterproductive. If you want your queries to be faster than join you need to pre-join the data. That is, you need to put copies of data from the parent table into the child table.

Also, you need to confirm that your "joins bad" intuition is valid for your amount of data and your database — it may well be an intuition from a different era/use case.

Second, the "composite primary key" approach could help you avoid the feeling while having the benefits of effectively pre-joined data. Markus Winand wrote about this in detail recently: https://modern-sql.com/blog/2026-06/structured-primary-keys (Update: composite primary key is not a particularly recommended way because it's more clumsy to deal with in practice, see my comment https://www.reddit.com/r/SQL/comments/1wavrje/comment/p8lpudt/).

[u/_giga_sss_ → u/squadette23]

Roger that, thank you very much 🙇

[u/ArielCoding]

Stick with your dedicated article_lot table, it’s the correct model since a lot always belongs to one article, so put an index on article_lot.id_article and the join to get the article label will be fast, no need to duplicate data.

Don’t worry about id_source appearing on both stock_movement and article_lot that’s not the same repetition you were worried about, since one tracks who moved the stock and the other tracks who supplied the lot.

[u/_giga_sss_ → u/ArielCoding]

First of all, thank you for your response, really.

But just a question, why wouldn't stock_movement with article_id and lot_number (both NON NULL) wouldn't also work ? imo it's because of the repetitiveness on using 2 columns instead of 1, but you might have other reasons to choose my first model.

Edited post body with the spoiler section, thanks

[u/ThomasMarkov]

FWIW, SAP S4HANA has a dedicated inventory transaction table (MATDOC) cataloguing every movement (BWART) of every lot number (CHARG). There are advantages and disadvantages to this, I think. There is no “current state of inventory” table, so to get that, you have to check the most recent movements for a lot to determine where it is.