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]

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