4

I'm looking to design an inventory database that tracks a snack bar. As this would be a single person/computer access and need to be easily movable to another system, I plan to use SQLite as the DB engine. The basic concept is to track inventory bought from a wholesale warehouse such as Sams Club, and then keep track of the inventory.

The main obstacle I'm trying to overcome is how to track bulk vs individual items in the products database. For example if a bulk item is purchased, let us say a 24 pack of coke, how do I maintain in the product database, the bulk item and that it contains 24 of the individual items. The solution would be fairly easy if all bulk items only contained multiple of 1 item, but in variety packs, such as a carton of chips that contains 5 different individual items all with separate UPCs, the solution becomes a bit more difficult.

So far I have come up with the multiple pass approach where the DB would be scanned multiple times to obtain all of the information.

Product_Table
SKU: INT
Name: TEXT
Brand: TEXT
PurchasePrice: REAL
UPC: BIGINT
DESC: TEXT
BULK: BOOLEAN
BulkList: TEXT // comma separated list of SKUs for each individual item
BulkQty: TEXT // comma separated list corresponding to each SKU above representing the quantity

Transaction_Table
SKU: INT
Qty: INT
// Other stuff but that is the essential

When I add a bulk item to the inventory (A Positive Quantity Transaction), it should instead add all of it's individual items, as I can't think of any time I would keep in stock to sell the bulk item. I would like to keep the bulk items in the database however, to help receiving and adding them into the inventory.

Neal
  • 599
  • 8
  • 16

2 Answers2

3

one way to do it is to create a 1:N mapping between bulk objects and their contents:

create table bulk_item (
  bulk_product_id integer not null,
  item_product_id integer not null,
  qty integer not null,
  primary key(bulk_product_id, item_product_id),
  foreign key(bulk_product_id) references product(sku),
  foreign key(item_product_id) references product(sku)
);

A comma-separated list is certainly fine (it might make it harder to do certain queries such as find all bulk objects that contain this SKU etc...).

jspcal
  • 50,847
  • 7
  • 72
  • 76
  • Thanks, I wrestled with creating a separate table containing purely bulk items. However, there are times in which there may be different bulk items that contain a common individual item. For example there may be several variety packs of chips and all three packs have nacho Doritos. You solution was my first inclination until i realized that the individual items could be associated with more than 1 bulk pack. – Neal Jul 08 '11 at 03:04
2

I have to both agree and disagree with jspcal. I agree with the "bulk_item" table, but I would not say that it's "fine" to use a comma separated list. I suspect that they were only being polite and would not endorse a design that isn't in first normal form.

The design that jspcal has suggested is commonly called "Bill of Materials" and is the only sane way to approach a problem like composite products.

In order to use this effectively with your transaction table, you should include a transaction type code along with the SKU and quantity. There are different reasons why your stock in any given SKU might go up or down. The most common are receiving new stock and customers buying stock. However, there are other things like manual inventory adjustments to take into consideration clerical errors and shrinkage. There are also stock conversions like when you decide to bust up a variety pack into individual products for sale. Don't think you can count on whether the quantity is positive or negative to give you enough information to be able to make sense of your inventory levels and how (and why) they've changed.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • Thanks, I agree the Bill of Materials example you linked describes just what I need. The problem is implementing it cleanly. However, the example given does not match exactly as the rule gives a product code matching only 1 place. And in my situation a product code could match multiple places. It's really a N:N mapping rather than a 1:N mapping as jspcal suggested. I wasn't clear in my initial description. Although i'm not as worried about mapping from the individual item back to the bulk items, so a one way mapping would be ok. – Neal Jul 08 '11 at 03:10
  • 1
    jspcal has suggested N:M. The bulk_item table is an intersection table (many to many) where both the parent and the child are products. You might have your Doritos Blazin' Jalapeno as part of a party variety pack, a Sam's bulk pack, a Halloween pack and on its own. You still only need the one child table. – Joel Brown Jul 08 '11 at 13:44
  • @JoelBrown we also store bulk goods like 24 pack of coke. I started to think about inventory db schema, probably all transactions (debits, credits) should have reference on skuId only. I'm wondering how to deal with situations when you unloaded 2 packs of coke (2 of sku1), then dispatched 1 can of coke. (1 of sku0). Could you help pls) – DmitriBodiu Nov 18 '20 at 15:19
  • 1
    @DmitriBodiu this is a common scenario in warehousing, distribution and retail. There are more kind of inventory movements than just sales. There are also returns and pack-breaks, like what you're describing. When you break one SKU into other SKU(s), it won't usually be on a one to one basis. Your bill of materials will tell you how many of one SKU are in another (2 cans of Coke in a 2-Pack of Coke), so you'll know how many to debit and credit against each SKU. You want to add a `type_code` to your inventory movement header to describe whether it's a sale, a return or a pack-break. – Joel Brown Nov 18 '20 at 15:39
  • @JoelBrown Regarding type_code yes! Each movement has TransactionId, one transaction may have multiple debilts and credits. Transaction could be everything what triggers movements(DeliverySheduled, ItemReserved). Its difficult to understand what to do for pack-breaks. If I have + 2 of sku0(pack of 24 coke cans) on locationA (unloaded), then someone dispatches 2 cans, I must register pack-break transaction, and do -1 of sku0, + 24 of sku1 (can of coke) on locationA, and then dispatch transaction: -2 sku1, +2 sku1 on TruckA? those 22 sku1 remaining are not sku0 anymore? – DmitriBodiu Nov 18 '20 at 16:05
  • 1
    @DmitriBodiu - Yes, that is it exactly. Think about it in practical, physical terms. If you break down a pack of cans (open the box or cut off the plastic wrapper) then you don't have that pack anymore. Instead you have a bunch of loose cans. It's like you "sold" a pack and "bought" 24 individual cans for your inventory - except of course that you are "buying" from and "selling" to yourself (i.e. within your own inventory). – Joel Brown Nov 18 '20 at 17:01
  • @JoelBrown thanks a lot! My scenario is a bit different, but maybe the same. We track customer's goods, we don't own them. And we have coffee in bags 25 kg, lotA, on pallet, 55 bags on 1 pallet. that is 1 sku. Then customer sells 2 bags of coffee to their clients, we need to track that we have full skus 55 bags on pallet, and one pallet with 53 bags. How can I track that? should I tag somehow those 53 skus(bag of 25kg of coffee) with 'PalletId' metadata? – DmitriBodiu Nov 18 '20 at 17:15
  • Also the same coffee could be stored in bulk on the ground, we know we have 2 tons of cofee on Floor location. Should I create a skuA = for 1kg of Coffee, lotA, bulk. And track skus, so 2 tons would be 2000 skuA? My question is should my movements have Amount decoupled from sku, like 50kg of sku1, or 30 each of sku2? or quantity better to involve in sku already, i.e skuA = 1kg of coffee, lotA, bulk. or 1 bag of coffee, lot A, 25 kg and just cound Sku's always (amount in this case could be just int or long) – DmitriBodiu Nov 18 '20 at 17:19
  • @JoelBrown what it makes more complex is that at some point in time our pallet could be a Sku, cause customer may ask to pack pile of coffee into bags of 25 kg with 55 bags on the pallet and ship out. so in this case packing is another transaction and we should link movements to pallet (sku1). One question which is important should my debits always equal to credits? if not, should system throw exception? i.e -2 tons of coffee, - 10 pallets, -550 bags, + 10 sku1(pallet with 55 bags of coffee, 25 kg) – DmitriBodiu Nov 18 '20 at 18:59
  • 1
    @DmitriBodiu When it comes to assigning SKUs you should not try to over-think it. Tie the SKUs to units that you can realistically sell. Don't have a SKU for "skid with 53 bags out of 55 possible" - That is not reasonable. If you could sell a full skid, then consider having a SKU for a full skid and one for a single bag. Alternatively, you could ignore the fact that 55 bags make a skid and just have one SKU for 1 bag. The reason to have SKUs is for pricing and sales, not for inventory tracking. – Joel Brown Nov 19 '20 at 00:02
  • 1
    @DmitriBodiu Debits and credits should equal each other for each part of the inventory movement, but if you break a skid down into single bags that's actually two (related) inventory movements. Think of breaking a skid down into bags as selling a skid and buying 55 bags. Inventory of skids goes down by 1 in storage and up by 1 in an internal transfer department. This is equivalent to a customer sale account, the number never goes back down. Then the internal transfer department gets an inventory of 55 bags (this is like a supplier delivery) which then moves to storage. Each half balances. – Joel Brown Nov 19 '20 at 00:08
  • @JoelBrown "The reason to have SKUs is for pricing and sales, not for inventory tracking." - we are doing just inventory tracking. Its not our goods, we just provide warehouse storage and help with distribution (repack goods). Customer performs sales on his own, then notifies us that a truck will come tomorrow to pick up the goods. He calls our representative to reserve 57 bags. Then we prepare 1 full pallet + 1 partial pallet (2 bags) for him. And goods are gone, I need to see in my reports that I still have 5 full, and 1 partial with 53 (cause 2 bags were dispatched out of the full). – DmitriBodiu Nov 19 '20 at 07:06
  • @DmitriBodiu You may not be selling the goods to the end customer, but you are moving them as if you were selling them. Order fulfillment is identical to sales from an inventory tracking perspective. However, my point is that you keep SKUs for the units which are saleable. If you are actually just fulfilling an order for 57 bags, then maybe all you need is one SKU for a single bag. It's up to the order picker to decide how to fill the order physically. There's no reason to track full skids if you are getting orders expressed only in bags. – Joel Brown Nov 19 '20 at 12:05
  • @JoelBrown my main problem is how to do the math. if I have 10 pallets (55 bags of coffee in each of them) on location A. And I need to break down one pallet, i.e take out one bag and throw it away, what debits and credits should I generate? So in my reports I can see 9 full pallets and one pallet with 54 bags? – DmitriBodiu Dec 23 '20 at 12:51
  • @DmitriBodiu I don't think I would have a SKU for a pallet of 54 bags if a normal pallet is 55. I would have a SKU for a single loose bag and maybe a SKU for a pallet of 55 bags. Honestly I don't think I would bother with the full pallet SKU if all the sales are in bags in any case, since it really isn't very interesting from an accounting perspective. Nevertheless if you have a SKU for a pallet and a SKU for a bag, then you would "sell" one pallet and "buy" 55 bags to break the pallet down. Then you can "sell" (or throw out) one bag. Now you have 54 bags left in your inventory. – Joel Brown Dec 23 '20 at 14:06
  • @JoelBrown so I do credit -1 SkuA(pallet with 55 bags) from locationA and debit 54 SkuB (one bag) on locationA, and debit 1 SkuB (one bag) on WrittenOffGoods location. Smth like this? – DmitriBodiu Dec 23 '20 at 14:09
  • @JoelBrown my problem is how to specify that one bag from a specific pallet, packed into in a specific box shifted to another box which is on another pallet on another location. At the same time i could shift the whole pallet with all its hierarchical structure of the goods. How to model debit & credit events to support that... – DmitriBodiu Dec 30 '20 at 08:23
  • @DmitriBodiu I think you are making this more complicated than it needs to be. Physical locations don't need to know _which_ pallets are in them. If you have a SKU for a pallet then that is a generic thing, not a specific physical thing. I believe you're trying to think of a pallet as a kind of physical location, and then tracking what is in the pallet. This is not what most distribution businesses would do. If for whatever reason you're sure you need to think of pallets as movable bins, then don't have a SKU for pallets. Treat the pallet as a bin and allow bins to contain other bins. – Joel Brown Dec 30 '20 at 15:28
  • @JoelBrown can you pls provide an example how to treat the pallet as a bin and allow bins to contain other bins? – DmitriBodiu Jan 04 '21 at 07:04
  • @DmitriBodiu I don't know how you organize your bins in your warehouse, but say for example your bin numbers are made up of row+bin+level, so an example might be 10-17-A for the 17th bin in the 10th aisle on the ground. Pallets move around so you need a different scheme for identifying them. One sensible way would be to give each pallet a bar code that encodes a unique number, say: 1234567. Your physical location tables need to have a surrogate key (e.g. SQL IDENTITY) because the two schemes of identifiers (bins and pallets) are not compatible with each other. – Joel Brown Jan 04 '21 at 20:55
  • @DmitriBodiu Second, you'll probably want a partitioning attribute on your physical locations table that says whether any given location is a bin or a pallet. This could be a single character code. Third, you'll want to have an optional "contained_in_location" column on the table that is a foreign key back to the same location table so that the record for pallet 1234567 is currently contained in bin 10-17-A. For the coffee bags themselves, you will store them either on pallet 1234567 or directly in bin 10-17-A, whichever you like. You probably prefer to store bags of product on pallets. – Joel Brown Jan 04 '21 at 20:59