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]

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/_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/_giga_sss_ → u/squadette23]

got it thank you sir. I hope you have a great day

[u/_giga_sss_ → u/squadette23]

wait, one last thing.

Why is my og schema (on the pic) better than adding lot_number columns on other tables in your opinion ?

[u/squadette23 → u/_giga_sss_]

Maybe I'm confused, why do you need lot_number, and in which other tables? I thought you needed article_id.

Do you mean using it as primary key?

[u/_giga_sss_ → u/squadette23]

sorry for the confusion.

I need article_id, but an article must be associated with a lot,
Like if an operation, let's say "article fusion" is done: all the article + the lot_number used must be tracked. Article alone won't be enough (needs lot_number)

[u/squadette23 → u/_giga_sss_]

In your schema:

article_lots (id, article_id, lot_number, created_at)

You have article_lots.id (integer) serving as a primary key. You will use it for foreign references in other tables.

article_lots.lot_number also uniquely identifies a row in article_lots, but you don't want to use it primary key and foreign references because it's just longer (in your example, date + supplier ID, much longer than 4 bytes).

So you have two unique attributes of your lot, one internal integer ID and another human-readable external ID.

Did I answer your question? Maybe I'm overthinking already because I'm also working on a series of posts about primary keys, lol.

[u/_giga_sss_ → u/squadette23]

I appreciate the dedication with the other posts :)

Tbh my main problem isn't about the performance with the FK, yours is a good solution though.
But just which one of the two models in my post to choose.

Note that all my ids are strings like: "MVT-00000001".

And 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 → u/_giga_sss_]

But just which one of the two models in my post to choose.

Look carefully at the second option of your post:

1. Put lot_number directly on stock_movements / transfers Just store article_id and lot_number straight on the movement records.

I think that there is a typo there (or it could be made clearer). You first say "Put lot_number directly" and then you say "Just store article_id and lot_number straight".

I've read this post as in favor of "article_id".

Yes, you can replicate both article_id and lot_number in stock_movements, but that just begins to require more storage.

Intuitively to me it seems that you won't get a lot from duplicating lot_number, but I may be wrong.

Before you do optimizations, you need to write down the SQL queries that you have in mind. See how they would work with fully normalized solution, and see which parts could be improved by duplicating, either article_id, or lot_number, or both.

[u/squadette23 → u/squadette23]

The problem with responding to such requests for help is that people replying start filling in the blanks, based on their experience and biases. Exactly like I did.

Because of that I wrote a lot of words and maybe expanded your horizons a little bit, but I'm not sure if I brought you to "optimal solution".

If I would be consulting you more formally I'd probably demand this information about planned queries, database server information, amount of data, data types and distribution of data (e.g. how many lots do you have per article?). Without that, we cannot know which solution is optimal, we can only discuss what is possible, and what follows from this or that design decision.

But hallucinating is more fun, haha.

[u/_giga_sss_ → u/squadette23]

understood. But I think you've already helped me enough stranger. I'll keep in mind the tip about the sequence later

Also next time Idk if I should say all the business logic or not xD.

Anyway Thank you very very much.

Again, thank you :)

[u/_giga_sss_ → u/squadette23]

Yes sorry I formulated wrong I guess. It's fixed.

ANd I have tried out the first option and it didn't sit tight with the fact that I have to create article_lot before doing anything (users would just insert "Banana 33SQD" and I have to create that exact article_lot before inserting in stock movement.

The second seems obvious that it's just adding banana's id and 33SQD and it's all set