Database Design: Inventory and Sales System?

Question

I need to develop an inventory and sales system.

For inventory, I need to be able to track ideal inventory levels, current inventory levels, point of order, value, selling price, etc.

Not every item in your inventory is “for sale.” For example, I can keep an inventory of plastic cups used for soda. Meaning, every time I sell soda, I need to subtract it from the stock count of the plastic cup. Thus, the “middle coke” is actually a plastic cup, some wipes and liquid, each element has its own current stock levels, cost, etc.

Then there is the concept of "combo". Perhaps the average Coke dollar and a $ 3 hamburger are sold together as combos for only $ 3.50 (saving $ 0.50). It was mentioned that Coca-Cola contains some wipes. Let's say a hamburger also includes napkins on its own. However, as a combo, the buyer does not receive a napkin for coke and a hamburger; rather, the buyer receives as many napkins as if he were buying only Coke.

For the sales system, I need to track every sale and, possibly, maintain relationships with inventory records (this would mean that I could never really delete an item in my inventory after the sale - for historical purposes). When I sell “medium coke” for $ 1, maybe I should break it down into $ 0.90 for a fluid and $ 0.10 for a plastic cup.

And when I sell "combos", maybe I will need to indicate that the hamburger really sells for $ 3, and the average coke is only $ 0.50 (only soda was reduced to make combos more attractive).

This may not be a new problem. Does anyone have any ideas (or examples) that I can look at to solve this problem? I’m not sure how to simulate inventory, goods sold (especially combos) and how to record sales,

Answer 1

The solution you are looking for will be based on an accounting style model and a couple of specifications (BOMs). Basic entity types will include:

• SKU This is a list of the things you sell. This property will include such things as a product description and current retail price. You can get imagination and break down the price of a children's table that gives prices over time. Suppose you are about to leave this wrinkle for now. Some SKUs may be “combos” of the type you are talking about.

• COMPONENT . This is a list of the things that make up SKU, such as napkins, cups, rolls, pies, coke syrup, etc. - to use your example. Just as SKU has descriptions and prices, COMPONENT have descriptions and unit costs. (Which can also be construed in a child table.) In this table, you usually store your ROP.

• COMPOSITION This is a specification that crosses SKU and COMPONENT and says how many units of each COMPONENT are in the SKU. You need one of them to cross two SKUs (for combos). You can use one table or two tables for this. Two tables will keep purists happy, one table will be useful from an encoder point of view.

• SALE . This is a transaction table that provides a heading for the sale record of one or more SKUs. This table will have things like transaction date, cashier identifier and other title elements.

• SALE_ITEM . This is a transaction details table that will include which SKU was sold (and how much) and how much. How much does SKU price denormalization cost at the time of sale, but can also include any special price overrides. The price actually charged for the SKU is a good thing to denormalize, because someone can edit the price of the list in the SKU, and then you lose information about how much was actually imposed on this product at that time.

• INVENTORY_HDR . This is a transactional table similar to the SALE concept, but it is the heading for an inventory transaction, for example, getting new inventory, using inventory (like selling it) and adjusting inventory. Again, this will be date / description material, but it may include a direct link to SALE_ITEM for inventory movements that are sales if you want. You do not have to do this, but some people like to establish a connection between the income and expenses of a transaction on a transaction basis.

• INVENTORY_DTL . This is a part for inventory transaction. This indicates which COMPONENT is going in or out, the amount that went in or out, and the transaction INVENTORY_HDR to which this motion was applied. It will also be where you store the actual cost paid for the component element.

• LOCATION . You can (if you want) also track the physical location of the inventory that you receive and use / sell. In a restaurant, this may not matter, but if you have a chain, or if your restaurant has a warehouse for a warehouse for the ingredients of the ingredients, then you don't care.

Consider the following ERD:

To make your income statement, you add up the money recorded in the SALE_ITEM table.

Stock levels are calculated based on the summation of the INVENTORY_DTL inputs and outputs for each COMPONENT component. (Do not store current stock levels in a table - this is doomed to matching problems.)

To carry out cost accounting, you will add the money recorded in the INVENTORY_DTL table. Please note that you usually don’t know exactly which napkin or roll you sold, so you won’t be able to associate certain components with specific SKU sales. Instead, you need to have an agreement to determine which components were used for any given SKU. You may have accounting rules that determine which agreement you should use. Most people use FIFO. Some industries use LIFO, and I even saw weighted average cost accounting.

Answer 2

I would suggest completely separating inventory tables from cash accounting tables. For example, the example you gave, I know this is ridiculous: for your average fast-food restaurant, a $ 0.90 coffee shop costs about $ 0.05 - $ 07 per cup and less than a penny for liquid, leaving a tidy profit for $. 83ish. Why should the cost be up to $ 0.90?

Tables:

InventoryItems : InventoryItemId, Name, CurrentInventoryLevel, IdealInventoryLevel

InventoryChangeRecords : InventoryChangeId, InventoryItemId, Change (int)

RetailItems fields: RetailItemId, Name, Price

RetailItemMakeup : RetailItemId, InventoryItemId, quantity

SaleTransactions fields: SaleTransactionId, DateTime, TotalSale

SaleTransactionItems fields: SaleTransactionId, RetailItemId, Quantity

For each sale, you need to use sproc (or trigger) to update CurrentInventoryLevel and insert records into InventoryChangeRecords. You can easily understand how any sale affected inventory by joining SaleTransaction to SalesTransactionItems to RetailItemMakeup.

If you want to make this even more powerful (but IMO an extraneous one), you can create another many-to-many table between SalesTransactionItems and InventoryChangeRecords.