Database Design: inventory and sales system?

Question

I need to develop a inventory and sales system.

For inventory, I need to be able to keep track of ideal stock levels, current stock levels, reorder point, cost, selling price, etc.

Not every item in the inventory is "sellable." For example, I may want to keep inventory of plastic cups used for sodas. Meaning, each time I sell a soda, I need to subtract one from the plastic cup's inventory count. Thus, a "medium coke" is actually the plastic cup, some napkins and the fluid, each item having its own current stock levels, cost, etc.

Then there is the concept of "combos." Perhaps a $1 medium Coke and a $3 hamburger are sold together as a combo for just $3.50 (a $0.50 savings). The Coke was mentioned to include some napkins. Say the hamburger also includes napkins on its own. However, as a combo, the buyer does not get the napkin for the Coke and the hamburger; rather the buyer only gets the same amount of napkins as if he/she were buying just the Coke.

For the sales system, I need to keep track of every sale and possibly maintain a relationship with the inventory records (this would mean that I could never really delete an item in the inventory once a sale is made -- for historical purposes). When I sell a "medium Coke" for $1, perhaps I should break it down as $0.90 for the fluid and $0.10 for the plastic cup.

And when I sell a "combo", maybe I need to be able to specify that the hamburger actually sold for $3 and the medium Coke was just $0.50 (only the soda was discounted to make the combo more appealing).

This can't be a new problem. Does anyone have any ideas (or examples) I can look at to solve this problem? I'm not sure how to model inventory, the sellable items (especially the combos), and how to record the sales.

Answer 1

The solution you are looking for will rely on an accounting style model and a couple of bills of materials (BOM). Your major entity types will include:

• SKU This is the list of things that you sell. It's properties will include things like product description and current retail price. You can get fancy and break price out into a child table that gives prices over time. Let's assume that you are going to leave that wrinkle out for now. Some SKUs can be "combos" of the sort you are talking about.

• COMPONENT This is the list of things that make up a SKU, such as napkins, cups, buns, patties, coke syrup etc. - to use your example. Just as SKU has descriptions and prices, COMPONENTs have descriptions and unit costs. (Which can also be historized in a child table.) This table is where you would typically store your ROP too.

• COMPOSITION This is a BOM which intersects SKU and COMPONENT and says how many units of each COMPONENT go into a unit of a SKU. You need one of these to intersect two SKUs too (for combos). You can either use one table or two tables for this. Two tables will keep the purists happy, one table will be expedient from a coder point of view.

• SALE This is a transaction table that provides a header for recording a sale of one or more SKUs. This table would have things like transaction date, cashier ID, and other header items.

• SALE_ITEM This is the transaction detail table that would include which SKU was sold (and how many) and for how much. The how much is a denormalization of the SKU price at time of sale, but could also include any special overrides to the price. The price actually charged for the SKU is a good thing to denormalize because someone could edit the list price in SKU and then you'd lose track of how much was actually charged for the item at the time.

• INVENTORY_HDR This is a transactional table that is similar to the SALE conceptually, but it is the header for an inventory transaction, such as receiving new inventory, using up inventory (as in selling it) and for inventory adjustments. Again, this would be date/description stuff, but it can include a direct link to a SALE_ITEM for inventory movements that are sales if you like. You don't have to do it that way, but some people like to establish the connection between revenues and costs on a transaction by transaction basis.

• INVENTORY_DTL This is the detail for an inventory transaction. This indicates which COMPONENT is going in or out, the quantity that went in or out, and the INVENTORY_HDR transaction that this movement applied to. This would also be where you keep the actual cost paid for the component item.

• LOCATION You can (if you wish) also track the physical location of the inventory that you receive and use/sell. In a restaurant this may not be important but if you have a chain or if your restaurant has an offsite warehouse for component ingredients then you might care.

Consider the following ERD:

To do your revenue accounting you would be adding up the money recorded in the SALE_ITEM table.

Stock levels are calculated based on adding up the INVENTORY_DTL ins and outs for each COMPONENT. (Don't store current stock levels in a table - This is doomed to cause reconciliation problems.)

To do your cost accounting you would be adding up the money recorded in the INVENTORY_DTL table. Note that you won't usually know exactly which napkin or bun you sold, so it won't be possible to link specific component reciepts with specific SKU sales. Instead, you need to have a convention for determining which components were used for any given SKU. You may have accounting rules that specify what convention you are required to use. Most people use FIFO. Some industries use LIFO and I've even seen weighted average cost accounting.

Answer 2

I would suggest completely separating the inventory tables from the money accounting tables. For example, the example you gave I know to be ridiculous: For your average fast food restaurant a $.90 cent Coke costs about $.05-$.07 for the cup, and less than a penny for the liquid, leaving a tidy profit of $.83ish. Why do the costs have to add up to $.90?

Tables:

InventoryItems fields: InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevel

InventoryChangeRecords fields: InventoryChangeId, InventoryItemId, Change (int)

RetailItems fields: RetailItemId, Name, Price

RetailItemMakeup fields: RetailItemId, InventoryItemId, Quantity

SaleTransactions fields: SaleTransactionId, DateTime, TotalSale

SaleTransactionItems fields: SaleTransactionId, RetailItemId, Quantity

For each sale, you should use a sproc (or trigger) to update CurrentInventoryLevel, and insert records into InventoryChangeRecords. You can easily figure out how any sale impacted inventory by joining from SaleTransaction to SaleTransactionItems to RetailItemMakeup.

If you wanted to do it in an even stronger (but IMO extraneous) manner, you could create another many-to-many table between SaleTransactionItems and InventoryChangeRecords.