Design – Inventory Management System Design Problem: ‘Items Packages’

database-designdesign

I am developing a retail management system with inventory management module, I am facing a logic design problem and I don't really know how to address the problem in an easy way, so I wrote an example which illustrates that problem:

Let's say I have an item (e.g. Cola Can), this item have a Barcode (11…1), this is so good until now and we can sell the item smoothly using the Cola Can barcode(11…1). However, when the warehouse buys cola can they buy with larger package for simplicity 6 cans packages which have different barcode(11…2), after that the 6 packages is braked down into 1-1 cans and sold per item of barcode (11…1).

The problem is that you buy with package and sell per item which will make the reports for the inventory incorrect, and the inventory stock will never match.

How to control this packages problem or should the system deals with the smallest package only (e.g. when I buy 6 cans package, the warehouse should enter 6 items of single can barcode)?

Best Answer

You're going to need to bust the assembly when an item within it is sold. Add the remaining items from the assembly to inventory and subtract the case. This will effectively balance out your inventory.

As per request via comments, I will adjust my answer accordingly.

We can use your 'Cola Can' example to help us visualize. We all know that you can buy 12 can cases of Cola 'Case Upc (11..2)', but lets say we want to sell them individually 'Unit Upc (11..1)'. In addition for clarification, let's say that your physical and logical inventory match in that all Single Cans have been sold. The process would go something along the lines of this.

if (inventory of single cola == 0) and (inventory of case cola > 0) then
    subtract 1 case from inventory of case cola.
    add the number of cola in 1 case of cola to single cola inventory
    subtract 1 single cola from single cola inventory

While the code is a bit rough, the example should show you how to effectively get your result.

Related Solutions

Design – Inventory management system design problem

There are a number of potential gaps in this analysis that may or may not pose a problem in a particular implementation. I would argue that some of them lie way outside of the topic of the site (accounting and supply chain management).

However, there is one gap that you will definitely need to close based on the facts mentioned in the question.

You will need to split the term "Item X" into at least two concepts already at the configuration side; inventory item from your site's perspective, and a supplier's item. For example, stock levels or the link to a corresponding retail item are attributes of the former. Order code is an attribute of the latter. The mapping between these two concepts will likely evolve as suppliers pop into and out of business relations with you.

Database Schema for Pricing Products (packages, promotions, qty based, limited time offer…)

Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody.

Table Structure
Table_Offer
--------
ID FK
Name   
start time  
end time   

MandatoryGroup
--------
ProductId (FK to product)

MixGroup1
--------
ProductId (FK to product)


MixGroup2
--------
ProductId (FK to product)


Table_offerDetails
------------------
ID PK
offerId (FK to table_offer)
MandatoryProduct(FK to MandatoryProducts.productID
firstProduct ( fk to MixGroup1.productId)
secondProduct (FK to MixGroup2.productId)

Offer
----
 ID    name    startDate  EndDate 
---    ----     --------   ------
1       COMBO   


MandatoryGroup
-------------
 ID    name 
 ---   ---- 
1      A
2      B


MixGroup1
---------
 ID    name 
 ---   ---- 
 3      P      
 4      Q

MixGroup2
---------
 ID    name 
 ---   ---- 
 5      x      
 6      Y
 7      Z



 OfferHeader
-------------
ID  Customer count(B) B_disc time_disc  stat_disc DiscTotal orderPayableTotal
--  --------- -------  ----   --------  ---------  --------  ------------    
1     BOB     2         20      5         2

OfferDetails ------------

 ID   offerID   MandatoryProduct     1stProduct    2ndProduct
----  -------    ---------------     ----------    ----------
1       1         A                     P              X
2       1         A                     P              Y
3       1         A                     P              Z
4       1         A                     Q              X
5       1         A                     Q              Y
6       1         A                     Q              Z
7       1         B                     P              X
8       1         B                     P              Y
9       1         B                     P              Z
10      1         B                     Q              X
11      1         B                     Q              Y
12      1         B                     Q              Z

To construct an offerDetails , you must provide a mandatory , a mixgropu1 and amixGropu2. So caseB , has only 2 offers as follows :

1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z
2B 2Q and 2X =2 offers
1A 1Q and 1X=1st offer
1B 1Q and 1X=2nd Offer ... etc other combinations are vivid.

Other offers are based on business logic: For quantity offer discount: create query to find the count of B in an offer for a customer . lets call it QTYB. find QTYB %2 and multiply it with quantityOffer value ( which is $20)

For statistics offer, Just add another bit called earnedPoint for a customer's purchase. and set the earnedPoint an expiry date. earned point can be the same as purchased amount. Check if the purchased amount >=$X , them issue discount as per policy.

Similar is the comparison of order time if its before 5PM and after 10AM then issue 5% else issue 10% discount.

Best Answer

Related Solutions

Design – Inventory management system design problem

Database Schema for Pricing Products (packages, promotions, qty based, limited time offer…)

Related Topic