Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Point of Sale and Inventory database schema

I’m trying to create a basic Point of Sale and Inventory management system.

Some things to take into account:

  • The products are always the same (same ID) through the whole system, but inventory (available units for sale per product) is unique per location. Location Y and Z may both have for sale units of product X, but if, for example, two units are sold from location Y, location Z’s inventory should not be affected. Its stocked units are still intact.
  • Selling one (1) unit of product X from location Y, means inventory of location Y should subtract one unit from its inventory.

From that, I thought of these tables:

  • locations

    • id
    • name
  • products

    • id
    • name
  • transactions

    • id
    • description
  • inventories_header

    • id
    • location_id
    • product_id
  • inventories_detail

    • inventories_id
    • transaction_id
    • unit_cost
    • unit_price
    • quantity
  • orders_header

    • id
    • date
    • total (calculated from orders_detail quantity * price; just for future data validation)
  • orders_detail

    • order_id
    • transaction_id
    • product_id
    • quantity
    • price

Okay, so, are there any questions? Of course.

  1. How do I keep track of changes in units cost? If some day I start paying more for a certain product, I would need to keep track of the marginal utility ((cost*quantity) - (price*quantity) = marginal utility) some way. I thought of inventories_detail mostly for this. I wouldn’t have cared otherwise.
  2. Are relationships well stablished? I still have a hard time thinking if the locations have inventories, or if inventories have several locations. It’s maddening.
  3. How would you keep/know your current stock levels? Since I had to separate the inventory table to keep up with cost updates, I guess I would just have to add up all the quantities stated in inventories_detail.
  4. Any suggestions do you want to share?

I’m sure I still have some questions, but these are mostly the ones I need addressing. Also, since I’m using Ruby on Rails for the first time, actually, as a learning experience, it’s a shame to be stopped at design, not letting me punch through implementation quicker, but I guess that’s the way it should be.

Thanks in advance.

like image 694
Andrés Botero Avatar asked Apr 06 '12 00:04

Andrés Botero


People also ask

Is a POS system a database?

POS solutions fall in to two main architectural categories. A "Database" POS has its own database in a closed and proprietary platform. Generally, these POS solutions work just fine until you start to scale your business and the complexities and risks of integration arise.

What is database schema with example?

In MySQL, schema is synonymous with database. You can substitute the keyword SCHEMA for DATABASE in MySQL SQL syntax. Some other database products draw a distinction. For example, in the Oracle Database product, a schema represents only a part of a database: the tables and other objects are owned by a single user.

What is an inventory database?

Inventory database is a centralized repository for all inventory data in an organization. Database for inventory management software allows balancing inventory costs and risks against the desired inventory performance metrics.

What is inventory table in SQL?

The inventory table describes the inventory based on a product ID. The inventory table is created with the following CREATE TABLE statement: CREATE TABLE INVENTORY ( PID VARCHAR(10) NOT NULL, QUANTITY INTEGER, LOCATION VARCHAR(128), PRIMARY KEY (PID) )


2 Answers

The tricky part here is that you're really doing more than a POS solution. You're also doing an inventory management & basic cost accounting system.

The first scenario you need to address is what accounting method you'll use to determine the cost of any item sold. The most common options would be FIFO, LIFO, or Specific Identification (all terms that can be Googled).

In all 3 scenarios, you should record your purchases of your goods in a data structure (typically called PurchaseOrder, but in this case I'll call it SourcingOrder to differentiate from your orders tables in the original question).

The structure below assumes that each sourcing order line will be for one location (otherwise things get even more complex). In other words, if I buy 2 widgets for store A and 2 for store B, I'd add 2 lines to the order with quantity 2 for each, not one line with quantity 4.

SourcingOrder
 - order_number
 - order_date

SourcingOrderLine
 - product_id
 - unit_cost
 - quantity
 - location_id

Inventory can be one level...

InventoryTransaction
 - product_id
 - quantity
 - sourcing_order_line_id
 - order_line_id
 - location_id
 - source_inventory_transaction_id

Each time a SourcingOrderLine is received at a store, you'll create an InventoryTransaction with a positive quantity and FK references to the sourcing_order_line_id, product_id and location_id.

Each time a sale is made, you'll create an InventoryTransaction with a negative quantity and FK references to the order_line_id, product_id and location_id, source_inventory_transaction_id.

The source_inventory_transaction_id would be a link from the negative quantity InventoryTransaction back to the postiive quantity InventoryTransaction calculated using whichever accounting method you choose.

Current inventory for a location would be SELECT sum(quantity) FROM inventory_transactions WHERE product_id = ? and location_id = ? GROUP BY product_id, location_id.

Marginal cost would be calculated by tracing back from the sale, through the 2 related inventory transactions to the SourcingOrder line.

NOTE: You have to handle the case where you allocate one order line across 2 inventory transactions because the ordered quantity was larger that what was left in the next inventory transaction to be allocated. This data structure will handle this, but you'll need to work the logic and query yourself.

like image 178
Brian Glick Avatar answered Oct 16 '22 06:10

Brian Glick


Brian is correct. Just to add additional info. If you are working into a complete system for your business or client. I would suggest that you start working on the organizational level down to process of POS and accounting. That would make your database experience more extensive... :P In my experience in system development, Inventory modules always start with the stock taking+(purchases-purchase returns)=SKU available for sales. POS is not directly attached to Inventory module but rather will be reconciled daily by the sales supervisor. Total Daily Sales quantities will then be deducted to SKU available for sales. you will work out also the costing and pricing modules. Correct normalization of database is always a must.

like image 41
koch Avatar answered Oct 16 '22 07:10

koch