The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

POS + Inventory design


I would like to ask about a design of an inventory system (warehouse - buy in bulk purchase) that integrates to a POS system (which is done in retail).

An item has qty. Now, a qty can be grouped. eg. in a box and each box also can be grouped inside a bigger box. but an item can be sold on a per item or per box, bigger box (pack) basis .

In reality, how many level of grouping does an item have? For  liquor system, the most common is per bottle and per case (2 levels). But with grocery or parts store, these are quite complex.

I'm planning to support 3 levels. per item, per box, and per pack (biggest box). and i'm planning to put a total_qty which is the total no. of items when multipled. e.g. 1 box = 12 items, 1 pack = 5 box (60 items). when a customer (POS) bought an item. e.g. 2 packs , the total qty is 5 (box) * 2 (pack) * 12 (items) = 120 (items) 120 items will be deducted to its total_qty. (warehouse)

Thanks for any comment.
Tuesday, August 19, 2008
it depends

do boxes and packs have their own sku/barcode or is just the same as 1 unit x qty in a pack

if they have their own sku/barcode, i would store then as a separate item, and do a stock transfer if a box was broken down

if they are all one barcode/sku and just different sized packs. most accounting software i have used, have auto-build inventory item
1 pack = 60x bottle
1 box = 10x bottle

that way you could make a auto-build inventory item. each time you sell a pack, it would decrease the inventory by 60 bottles, and increase the inventory by 1 pack

this way you can also support bundled inventory items
1x torch kit
- 1x torch
- 2x batteries
- 1x bag

and if you design your database tables properly, you won't be limited to 3 levels of grouping, you could create any number of levels of grouping you wish
Tuesday, August 19, 2008
We always supported 3 levels for warehouse/vending (case, box, each).  The largest size was warehouse, the trucks were usually issued the inner boxes, and the machines vended the retail.

However, for entry, you could enter in all three fields of a product during inventory.

The way the barcoding worked, the barcode on each size was unique, when it scanned and recognized the barcode, the barcode was attached to a product and size and would increment the appropriate field.

If you are scanning for everything (we usually only scanned at the warehouse, not at the driver filling machines), you don't really need the multi-entry fields, but if you are doing manual entry by product, you need to default to the unit they are most likely to be handling (warehouse, issue or retail).  For soft drinks, for example, the warehouse always issued cases, but the machine was filled by cans.  At that time, only the cans had barcodes, but given the small number of soft drink brands (and small number of brands per machine), I'm not sure they even used the scanner.  At the machine side, the machine configuration prompted the entry by brand, so there was no need for machine aided product identification help.

When the "form" was saved, only total individual units was ever saved to the database, the larger units were multiplied up at that point.

This was on a handheld with Intel small memory model, a very small screen and very small storage available.

I'm not sure how I would design the DB and UI 15 years later.  The UI we had was very advanced for its time, having inline calculator logic for easy calculations on any numeric field (kind of like Turbo Tax), and the very intelligent barcoding and auto-saving/auto-incrementing and error detection which would allow you to take inventory very quickly.
Cade Roux Send private email
Wednesday, August 20, 2008
+1 to handling them as "assemblies", where boxes, cases, crates, or however you want to do it can be created by the user on the fly, and can even consist of multiple kinds of items.

But if you want to try to keep it simpler and limit the levels, I would use what you suggested and add one more: pallet.  No one will ever sell a pallet at retail, but the item will be useful for tracking warehouse stock and incoming shipments.
Joel Coehoorn Send private email
Wednesday, August 20, 2008
Kits or assemblies are useful for sales, invoicing, etc (we used that in office coffee service), but you need to understand how they are really handled in practice at the handling and inventory level - this can vary by industry - in some cases they will be assembled into a separate permanent SKU and in some cases not.

For instance, in vending, they would never have wanted to do any kind of transfer between unit sizes - there simply isn't the time for that or need for that detailed level of recording - half of one box (of 60) plus half of another is still just 60 eaches and is the same as one box.
Cade Roux Send private email
Thursday, August 21, 2008
It sounds like a BOM (bill of materials) design.
I think BOM model can be implemented to what you need.
Miguel Crispin Send private email
Tuesday, September 09, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz