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!<

I think this is a very accurate reflection of the world we live in. This post my thoughts on the topic:

Most of us are like Borksen... or perhaps even worse. We hope to live comfortably, avoid trouble, and make it through life without getting involved. We are aware that suffering and injustice exist, but we often choose not to act because it is easier to look away than to confront uncomfortable realities.

That is one of the reasons I admire Togashi's writing. He is willing to explore difficult themes like apathy, responsibility, and humanity's tendency to ignore the suffering of others. Through his characters, he encouraged me to reflect on my own values and behavior (just like how Makoto Yukimura did me with Thorfinn).

Maaaaan I want to be a kinder and smarter person

Credits to the post that helped me realize it: https://www.reddit.com/r/HunterXHunter/s/QmV7bT55EP