The infamous POS app

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

The infamous POS app

Post by quailcreek » Sun Apr 03, 2016 7:00 pm

Hi,
I’m putting together a POS app. and I would appreciate opinions and suggestions from the forum, especially regarding the database setup.

I’m initially setting this up with a SQLite Db but it will end up running on a LAN with MYSql. I have barcode scanning sort of working so most things will be handled with a scanner.

Here’s what I have so far:
Customer Table: (the usual type info. custID. name, address...)

Employee Table: The usual info and (empPWD,empUserID,empPrivileges)

Distributors Table: (distributorName,contactName,contactPhone,contactEmail,distAddress,distCity,distState,distZip,accountNum) the accountNum is the businesses account with the distributor.

Inventory Table: (itemID,itemDistributor,itemBarcode,itemDescription,itemCost,itemPrice,taxableLocal,taxableState,taxableFed,itemManufacturer,Catagory1,Catagory2,Catagory3,itemSerialNum,onHand,itemNotes)

Repair Receipts: (itemDescription,itemBarcode,CustID,repairRecptNum,itemSerialNum) needs to track the serial number and cost, etc of the item being repaired.

Consignments Receipts: Used for selling customers used equipment. Needs to be used to track customer sales and serial numbers.

Sales Receipts: needs to be used to track customer sales history and to search and sort receipts by customer.

Categories Table: (Catagory1,Catagory2,Catagory3) for keeping track and sorting inventory.

Preferences Table: (taxRateLocal,taxRateState,taxRateFed,shopMin,shopRate) this area of the app also handles the refund and return policy text to print on sales receipts.

Questions:
1) I have the employee password, user ID and access privileges stored in the employee table. Is this a right place for these on a LAN with MYSql?

This one I'm not really sure how to handle.
2) Regarding inventory “onHand” quantities. When an items are added to inventory stock or sold, the quantities change… obviously. Should there be a row in the inventory table for each individual item and then delete the item from the table when it’s sold? Some of the items might be serially numbered making each one the same but unique. How should this be handled so I can get the onHand quantities as needed? Can the DB handle this internally somehow?

3) As far as linking the customer and items sold to the sales and repair receipts. I was planning to just use the custID for the customer part. For the items sold part, should I use INNER JOIN to the inventory table? And for the repair receipts, should I have a repairItems table and INNER JOIN to to that?

I hope my questions are understandable.
Thanks for the help.
Tom
MacBook Pro OS Mojave 10.14

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 316
Joined: Tue Apr 10, 2012 9:18 am

Re: The infamous POS app

Post by ghettocottage » Sun Apr 03, 2016 7:31 pm

In response to question #2, my initial thoughts:
2) Regarding inventory “onHand” quantities. When an items are added to inventory stock or sold, the quantities change… obviously. Should there be a row in the inventory table for each individual item and then delete the item from the table when it’s sold? Some of the items might be serially numbered making each one the same but unique. How should this be handled so I can get the onHand quantities as needed? Can the DB handle this internally somehow?
Since you are using your receipts as a sort of "join" table (to join Customers and Items) then you probably would not want to delete the Items from the table, but rather mark them as sold (perhaps a text-field for sold or unsold, or ...more simply..just a boolean field)

That way would be able to pull up a list of items each customer has purchased, with serial numbers and all. Also, you could use that information for viewing items sold in a certain time-period or whatever else. If your database becomes too large you could just have an event that finds items sold over a year ago (or whatever) and delete those items.

Your query for onHand quantities would search for unsold items.

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Sun Apr 03, 2016 9:58 pm

Thanks for the reply, ghettocottage .
That makes sense. You're right though, marking each item sold the Db could get very large. Providing sales trends was also something I wanted to put into the app. Seems we're thinking alike. :) I think I need to learn about events too.
Tom
MacBook Pro OS Mojave 10.14

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Tue Apr 05, 2016 3:46 pm

Still thinking about how to put this together. Would it make sense to have a "Quantities" table with 3 columns, quanID, quanBarcode and theQuantity. Then have this table INNER JOIN with the inventory table?
Tom
MacBook Pro OS Mojave 10.14

AxWald
Posts: 369
Joined: Thu Mar 06, 2014 2:57 pm

Re: The infamous POS app

Post by AxWald » Tue Apr 05, 2016 4:07 pm

Hi,

what you're describing here is commonly named an ERP. That is what I'm making my living with - helping ppl to find, setup & maintain it, then adding customer specific stuff to it - and that's where LC comes in: connecting the ERPs to web shops, POS terminals, tax advisers/ accountants/ authorities, writing special interfaces for quick information/ data entry, handling imports/ exports/ automatization and such.

Would I do what you're planning? NEVER EVER!
As soon as it's for more than a 1-man company such can easily become a task to devote your life to - and if it's not for yourself your customer/ employer may make your life a hell! Not only is money involved here; as we all know appetite comes with eating, and you'll be chased forever with implementing new features, repairing old bugs, explaining why this cannot be done, but that must be done - you get me?

I usually help the customer to find a suiting pre-made ERP (or use what's there). There's people that do ERPs all day long, year after year, and have more experience with such (and the legal prerequisites) than I ever could learn.
I prefer smaller but established ERPs where the developers are ready to collaborate, and to my delight there's quite some Open Source ones meanwhile. As I understand you're from the US so my contacts here will not help you, but I'm sure you'll not search in vain.

This way I can leave a lot of responsibility to the ERP people, these are the ones that deliver the database & the basic interface - and I can concentrate on the juicy stuff ;-)
Still that can be plenty of work, and I have customers that I work for for over a decade now!

But it's up to you. Getting a small ERP up & running can easily lead to small company, and to a nice income. Should you really want to try this, be aware that there's a lot of legal stuff to think of - how to handle currency & rounding in the database, how long to have your data available in what formats, how to handle the taxes - you'll want to get professional advice here!

A few points more:
quailcreek wrote:I have the employee password, user ID and access privileges stored in the employee table.
You will NEVER EVER store passwords anywhere! You ALWAYS hash & salt these!
quailcreek wrote:When an items are added to inventory stock or sold, the quantities change… obviously. Should there be a row in the inventory table for each individual item and then delete the item from the table when it’s sold?
You will NEVER EVER delete an article entry that has either been bought or sold once. Or your Sales/ Purchases employees will lynch you because their documents are corrupted! You'll mark it as "deleted" ...
quailcreek wrote:Some of the items might be serially numbered making each one the same but unique
In this case you'll have a "Serialz" table linked to your "articles" ;-)
quailcreek wrote:How should this be handled so I can get the onHand quantities as needed?
If I understand correctly: the "amount available" is a field of the article table. It will be modified by purchases (additional: not yet arrived), or by sales (additional: not yet delivered). And beware, you'll need to take care if one article can be purchased from more than 1 contractor ...
quailcreek wrote:I was planning to just use the custID for the customer part.
Generally, every table will have an "ID" (unique, auto-increment, not null). This ID is what you work with. And you can have "CustomersID"s as you desire, nicely formatted and in any flavor - but only for documents & for display etc. Your database has its own "ID"s to work with, and these are never to be displayed nor modified (well, sometimes, when you're feeling adventurous ...).

Hope I could help a little. Have fun!
Livecode programming until the cat hits the fan ...

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Tue Apr 05, 2016 5:09 pm

Thank you AxWald,
I completely understand what you're saying and it makes complete sense. I will definitely look into finding a willing ERP developer. That may be the simplest and easiest way yo go. I would still like to understand some things. What I'm trying to put together would be for the small shop, 1 to 3 users.
AxWald wrote:You will NEVER EVER store passwords anywhere! You ALWAYS hash & salt these!
So if the passwords are not stored someplace in the Db, how are they validated when the user logs in? By hash & salt do you mean encrypt?
AxWald wrote:You will NEVER EVER delete an article entry that has either been bought or sold once. Or your Sales/ Purchases employees will lynch you because their documents are corrupted! You'll mark it as "deleted" ...
I agree.
AxWald wrote:In this case you'll have a "Serialz" table linked to your "articles" ;-)
These serialized numbers would be established by the part manufacturer. Would this still be how you would suggest handling this?
AxWald wrote:If I understand correctly: the "amount available" is a field of the article table. It will be modified by purchases (additional: not yet arrived), or by sales (additional: not yet delivered). And beware, you'll need to take care if one article can be purchased from more than 1 contractor ...
Could you please clarify what you mean by field of the article table?
AxWald wrote:Generally, every table will have an "ID" (unique, auto-increment, not null). This ID is what you work with. And you can have "CustomersID"s as you desire, nicely formatted and in any flavor - but only for documents & for display etc. Your database has its own "ID"s to work with, and these are never to be displayed nor modified (well, sometimes, when you're feeling adventurous ...).
I understand this. I've developed a number of iOS apps with SQLite as the Db. I was just trying to explain how I intended to handle retrieving the customer information. Sorry for the confusion.
Tom
MacBook Pro OS Mojave 10.14

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 316
Joined: Tue Apr 10, 2012 9:18 am

Re: The infamous POS app

Post by ghettocottage » Tue Apr 05, 2016 5:57 pm

AxWald makes some good points, but it does mean it is an impossible task. If this project is for a small company you work for, and you are the in-house IT guy, it could work fine...and in some ways better than a pre-developed system, since you will be intimately familiar with it, and will be able to modify/customize as needed.

If it is for a company that has hired you just for this project, you might consider not developing this completely from scratch, and find something pre-made and customize as needed.

One thing you have not mentioned is what sort of payment gateway you will be using. If that will be a separate thing (for example, Paypal or similar service) it will not affect your project; however, if you are going to integrate your payment system with this database, or plan on storing credit-card payment information, than you will run into some challenges, since you will need PCI Compliance, which is a headache, to put it mildly.

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Tue Apr 05, 2016 6:15 pm

Thanks ghettocottage,
ghettocottage wrote:AxWald makes some good points, but it does mean it is an impossible task. If this project is for a small company you work for, and you are the in-house IT guy, it could work fine...and in some ways better than a pre-developed system, since you will be intimately familiar with it, and will be able to modify/customize as needed.
This is not the case but I still want to understand how to make this all happen. It sounds like you have put one pf these together, is that so?
ghettocottage wrote:If it is for a company that has hired you just for this project, you might consider not developing this completely from scratch, and find something pre-made and customize as needed.
This is the situation. The business is a startup and doesn't have mush money to work with. I've started looking for ERP developers or open source ERPs to see what options are out there, as AxWald suggested.
ghettocottage wrote:One thing you have not mentioned is what sort of payment gateway you will be using. If that will be a separate thing (for example, Paypal or similar service) it will not affect your project; however, if you are going to integrate your payment system with this database, or plan on storing credit-card payment information, than you will run into some challenges, since you will need PCI Compliance, which is a headache, to put it mildly.
We won't be storing any CC information but the app will eventually be tired to a cash drawer and CC reader. So I would guess your statement would apply to this project.
Tom
MacBook Pro OS Mojave 10.14

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 316
Joined: Tue Apr 10, 2012 9:18 am

Re: The infamous POS app

Post by ghettocottage » Tue Apr 05, 2016 7:29 pm

..It sounds like you have put one pf these together, is that so?
I have not put together a POS with products, but have put together a registration system for a mountaineering company. Similar concepts with a customer table and such.
We used Joomla as the platform to handle user-accounts. Looking back, I would not have used Joomla, but would have used some other CMS like Wordpress (or even Revigniter). Using an existing platform like that keeps you from having to invent your own password/user account system, and they are generally pretty good at regular security updates.

I designed the rest of the database for Courses, Registrations, etc.

You can avoid having to do PCI compliance if do not store payment information (credit card data).. PCI Compliance is a bear of a thing to deal with.

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Tue Apr 05, 2016 7:54 pm

Wordpress and Revigniter are both web based aren't they? I'll be running on a LAN.
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: The infamous POS app

Post by phaworth » Tue Apr 05, 2016 8:31 pm

I don;t quite have your db schema figured out, but one comment on the available quantity of an item. If you try to maintain that as a field somewhere adjusted by incoming inventory and purchased quantities, you will almost certainly run into issues where it is wrong. The best way to do this using good db design principles is to calculate the available quantity from your inventory and purrchased quantities using the SUM() function in your SELECT statement. This assumes you have an inventory transactions table of some sort that contains one row for each action that increases or decreases the inventory.

On the subject of PCI, I just implemented a system using Stripe. Using it, credit card info never hits your severs and goes directly to their servers so you don't have to worry about PCI. It's pretty easy to set up and their commission reates are about the same as PayPal.

Pete

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Wed Apr 06, 2016 12:10 am

Hi Pete,
I was hoping you would chime in on this. I appreciate the help very much.
phaworth wrote:The best way to do this using good db design principles is to calculate the available quantity from your inventory and purchased quantities using the SUM() function in your SELECT statement.
Sounds like a very clean way to handle that. I have an inventory table and an on order table. Is that what you mean? Or do you consider the inventory and purchased quantities to be the inventory table?
phaworth wrote:This assumes you have an inventory transactions table of some sort that contains one row for each action that increases or decreases the inventory.
So would this be a separate table that contains sales transactions and adding stock to the inventory and have an INNER JOIN to a sales table and the inventory table or visa versa?
phaworth wrote:On the subject of PCI, I just implemented a system using Stripe. Using it, credit card info never hits your severs and goes directly to their servers so you don't have to worry about PCI. It's pretty easy to set up and their commission rates are about the same as PayPal.
Stripe... I'll look into that.
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: The infamous POS app

Post by phaworth » Wed Apr 06, 2016 5:52 pm

I'd have to make some assumptions about your table structures to answer that.

If your inventory table has an entry for each action that causes the inventory to increase or decrease, it's pretty easy. Let's say the columns in it are:

ItemID (a foreign key to your items table)
Quantity (positive for additions to inventory, negative for reductions)

There'd be other columns of course but those would be the ones of interest for this exercise. The SELECT to get the current inventory would just be

SELECT SUM(Quantity) FROM Inventory WHERE ItemID=<whatever itemID you're interested in>

To return current inventory for all your items:

SELECT SUM(Quantity) FROM Inventory GROUP BY ItemID

If your inventory table only includes information about increases in Inventory and the orders table is where you would get the decreases, the SELECT is a little more complicated:

SELECT (SELECT SUM(Quantity) FROM Inventory WHERE ItemID=123) - (SELECT SUM(Quantity) FROM Orders WHERE ItemID=123)

Hope that helps,

Pete

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: The infamous POS app

Post by quailcreek » Wed Apr 06, 2016 6:42 pm

Thanks again, Pete.
phaworth wrote:If your inventory table has an entry for each action that causes the inventory to increase or decrease, it's pretty easy.
Wouldn't the inventory table become very large?
phaworth wrote:If your inventory table only includes information about increases in Inventory and the orders table is where you would get the decreases, the SELECT is a little more complicated:
SELECT (SELECT SUM(Quantity) FROM Inventory WHERE ItemID=123) - (SELECT SUM(Quantity) FROM Orders WHERE ItemID=123)
I was thinking:
Sales table: for tracking items sold. (subtract from inventory. Also used to track sales trends of each item.)
Repairs table: for tracking items brought in for repair and any inventory used for the repair. (subtract from inventory)
Inventory table: for tracking stock on hand. (and for adding to inventory)
Requisitions table: orders placed with distributors. (for adding to inventory when order arrives)

Does that make sense?
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: The infamous POS app

Post by phaworth » Wed Apr 06, 2016 8:31 pm

Yes, sounds good to me.
Pete

Post Reply

Return to “Databases”