Help creating scalable schema for POS

[hads]

Hi all,

I'm creating a Point of Sale application, mostly to teach myself about GUI programming and obviously some more about database design

I want to achieve the following with my database;

- Store a list of products - could be large, like 10,000
- Keep track of stock levels for said products
- Store all incoming (purchases) and outgoing (sales) transactions.

I've had a search around and either I can't come up with the right search terms or I just plain can't find anyone talking about this.

My feeble attempt at a designing the schema was something along these lines;

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `price` float(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `entered` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` enum('s','c') NOT NULL default 'c',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

CREATE TABLE `transactionitems` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `transaction` int(10) unsigned NOT NULL default '0',
  `item` int(10) unsigned NOT NULL default '0',
  `quantity` int(10) NOT NULL default '0',
  `price` float(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB

Using MySQL because I know it (maybe I should say "have used it before" here) and because I wanted to use it's replication.

Using the above I would keep a record of each incoming transaction with positive quantities in the transactionitems table and outgoing transactions with negative quantities.

When you wanted to see a list of stock with the stock level you would have to do something which looks grossly inefficient like;

SELECT i.id, i.name, i.price, SUM(t.quantity) AS stock FROM items i
LEFT JOIN transactionitems t ON t.product = i.id
GROUP BY i.id

The above might work for a short period of time with a small amount of items and transactions but I feel like I am missing some basic point of database design.

If someone who can understand what I am rambling about and has a clue could enlighten me I would be very grateful.

Cheers,

hads.

 

[MDXer]

Having built a couple data warehouses off POS and F&B systems this is pretty much the layout that is used. The items information are typicaly normalized into Product Cat, SubCat (Family), Group, Product.

But the Trans and Trans Items tables are the method I have seen used on large systems. Obviously there are numerous other supporting tables such as tender types and tender info, Authrization etc etc.

Very Large systems use multiple dataases and sometimes servers to handle the data volume. Where one will hold the Active or Open transactions which are then sent to a seperate enviroment for archiving for whatever time period closed transactions are kept for.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[hads]

Wow, that wasn't the answer I was expecting. Thanks MDXer, it's good to know that I am not completely database illiterate then.

The items information are typically normalized into Product Cat, SubCat (Family), Group, Product.

But the Trans and Trans Items tables are the method I have seen used on large systems. Obviously there are numerous other supporting tables such as tender types and tender info, Authrization etc etc.

Yep cool, my example above was simplified down to the stuff relating to what I was worried about.

I've just put 10,000 test products and 100,000 test transactions into a database to see what happened with the select statement in my last post. It bogged down my test server completely. I still feel like there must be a nicer way to do this, or something I am missing.

Thanks much for your input.

hads.


01101000011000010110010001110011

 

[sillysod]

In order to speed up the select statement you could add a table to record stock takes at certain points in time

So you would have a table which held fields like
Product, Qty and Date/Time etc etc maybe a reference to the transaction which was used to rectify any discrepencies

the point is if you have a stock take you record the total stock qty so instead of querying 100000 transactions you query the say 10000 most recent transactions since the stock take using the stock take qty as the opening balance.

Hope that makes sense

 

[hads]

Thanks sillysod, that sounds like a good idea.

I have also thinking about adding a stock level field to the items table which would be updated on each transaction to save having to do a join at all.

It seems less elegant but more efficient.

I'll now go away and think about the two options, see which one better suits the situation at hand.

Thanks for your input.


01101000011000010110010001110011

 

[ceh4702]

Well you have to think about the business processes. First you have to buy products. You need a list of vendors to buy products from with vendor codes. The vendors have to have ways to be contacted and need addresses. Then you need a list of products that you buy with your internal product code and the vendor product codes, descriptions, unit of issue and price. Then you need orders which should all have a unique number. Each order has items on the order, and a quantity for each order.

Ok Now you need an inventory system call it a warehouse. The inventory system should use your internal item numbers and the quantity. However, you will realize that even though you add something to inventory, the price is relative to the date it was purchased. i.e. the cost of an item is relative and chages over time. Suggest you have a current selling price. All businesses hide the actual cost of the items to the public so keep this in mind. Recommend you put all the cost info in a separate table. The current retail price that you quote could go into the inventory, but it may be some kind of calculation. In the cost table you may want to keep an historical record of what you paid for all the items, when they were purchased and what you determined was a fair price. Then you may have some way of offering discounts for bulk purchases.

Then there are Sales of items to each customer by date time each receipt has a individual number. On each receipt there are items with quantities and the individual price and the extended price based on the quantity purchased. Then you have to pay sales tax depending on your product type and applicable tax and the locatin of your store, delivery mode, and the location of the customer. Every state has different rules for tax.

Then there are delivery costs. How are you going to deliver the items, and how do you inform your customer of the delivery costs and the method for the calculation. You may have to store the weight of each item and have a method to compute delivery costs based on the shipper, and the delivery method. i.e. ground, next day air, courier, etc. This may have to be an on the fly computation based on a rate and some rules. They you may want to keep track of tracking numbers if that is possible in a shipping table, or an invoice table or both. You really need an order table and a shipping table. Sometimes they differ.

What happens if your inventory is wrong and/or a person wants an item to be delivered when the part comes in from an order? On the order if an item is not in stock the custom needs to authorize whether you drop the item from the order, the order is cancelled, or the item is ordered. So when you make out the orders you need to indicate on the order what the options are, and on the items if they are shipped, or ordered. If they are ordered do you have them delivered to you or to the customer on a drop shipment.

Then you need some accounting. Cash receipts for orders. Non-cash or credit can be in various forms. Credit cards, debit cards, pay pal, etc. Different types of cards may have different types of charges. If your entire system is run off of a credit card basis then the costs need to be built in. Then you need accounts receivable and accounts payable. Here is a scenario. Someone orders a product they have a bill and that triggers an account receivable for you. You try to charge the item on their credit card and it is rejected. You have to inform the customer to see if they have an alternate form of payment. Then you either cancel the order, or whatever. So you have to decide on the order of the business flow. 1. receive order. 2. collect credit. 3 either get paid and process order, or go back to customer with problems. 4 Ship orders only when payment received.

Do you see why a prepackaged software system is a good idea? There are lots of variables to consider.

First sit down and identify your business rules and then develop the processes and the information to be stored and then develop the tables to solve the business processes, and the legal requirements.

I did not mention it, but you have to keep records to cover operating exenses and maybe payroll, to develop tha actual income made by the business to report to the IRS to base your taxes on. You may also want to consider things like marketing, and advertising and service contracts with solution providers. For instance if you use credit cards you may need software from a company for processing online payments.

You can simplify the system by limiting the scope, but that is not realistic.

If you do not like my post feel free to point out your opinion or my errors.

 

[hads]

Hi ceh4702,

Thanks for your detailed post. I realise that there is a lot more to a POS system than my three tables above, the example I gave above was simplified down to things that related to the task at hand.

Yes, I see why pre-packaged software is a good idea, but the main reason for this project was to teach me about GUI programming and, as it turned out, database design. Besides, I don't actually own a store

Thanks again.


01101000011000010110010001110011