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

Re: The infamous POS app

Post by quailcreek » Thu Apr 07, 2016 2:04 am

So is the syntax for the itemBarcode correct? I want to prevent duplicate barcode numbers. I know I can check in LC before the data is written but I wanted to try to do it from the db.

Code: Select all

 put "CREATE TABLE inventory(itemID INTEGER PRIMARY KEY AUTOINCREMENT,itemDistributor TEXT,itemBarcode TEXT NOT NULL UNIQUE,itemQuantity TEXT
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 » Thu Apr 07, 2016 2:52 am

Yes and +1 for using the database to do the check. That guarantees that no matter what program inserts data (your app or an SQL admin program), no duplicate barcodes can get in.
Pete

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

Re: The infamous POS app

Post by quailcreek » Thu Apr 07, 2016 4:08 pm

Thanks, Pete.
I'm not at this point yet but I looked at Stripe. Does it work for a stand alone on a LAN or is it stickily used for web-based apps?

Have you looked at:
http://www.worldpay.com/us

As far as incrementing/derangementing the quantities in the inventory table. Should I be using a foreign-key between the salesReceipts and repairReceipts tables linked to the inventory table? If so could you give an example of how to set that up?
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 » Thu Apr 07, 2016 8:25 pm

I haven't tried worldpay but have heard good things about it. I've only used stripe in a web application but I'm pretty sure you could use it from Livecode by using the Post command to access their API.

Definitely use foreign keys. Assuming you had a column named ItemID in each table that hold the primary key value of the table that defines your items, the foreign key definition on the inventory table would look something like this:

itemID REFERENCES salesReceipts(ItemID),repairReceipts(itemID)

I can't quite remember if you can separate two foreign ket definitions with a comma or if they have to be separate REFERENCES clauses.

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

Re: The infamous POS app

Post by quailcreek » Thu Apr 07, 2016 10:39 pm

Here are the 3 tables I'm working on. As a recap here are some parameters I need allow for.
I need to increment/decrement the itemQuantity in the inventory table when items are sold or purchased from a distributor. Some of the purchased items carry a serial number some don't. The serial number makes that item unique so I think those will want to be marked as "sold" in the inventory table with a quantity of 0. What are your thoughts? Those items w/o serial numbers will reduce the itemQuantity when sold. For now I can handle adding items to the inventory.

I could use some help implementing the foreign keys to reduce the itemQuantity.
Do I have the foreign keys pointing to the right columns? Could you give me an example of using the foreign key to reduce the itemQuantity?

Code: Select all

## This is a table for keeping sales receipts
      put "CREATE TABLE salesReceipts(receiptID INTEGER PRIMARY KEY AUTOINCREMENT,itemSerialNum TEXT,itemDescription TEXT,itemBarcode TEXT,CustID TEXT,repairRecptNum TEXT,"&\
      "totalCost TEXT,itemQuantity TEXT,itemCost TEXT)" into tMyFiguresStatement6
      revExecuteSQL sDatabaseID,tMyFiguresStatement6
      
      if the result is not an integer then
         answer "Error creating salesReceipts table"
      else
         answer information the result && "salesReceipts table created"
      end if
      
      ## This is a table for keeping repair receipts
      put "CREATE TABLE repairReceipts(receiptID INTEGER PRIMARY KEY AUTOINCREMENT,itemSerialNum TEXT,itemDescription TEXT,itemBarcode TEXT,CustID TEXT,repairRecptNum TEXT"&\
      ",totalCost TEXT,itemQuantity TEXT,itemCost TEXT)" into tMyFiguresStatement6a
      revExecuteSQL sDatabaseID,tMyFiguresStatement6a
      
      if the result is not an integer then
         answer "Error creating repairReceipts table"
      else
         answer information the result && "repairReceipts table created"
      end if
      
      ## Create table inventory and tracking itemSerialNum
      put "CREATE TABLE inventory(itemID INTEGER PRIMARY KEY AUTOINCREMENT,theCount TEXT,itemDistributor TEXT,itemBarcode TEXT NOT NULL UNIQUE,itemQuantity TEXT,itemDescription TEXT,"&\
      "itemCost TEXT,itemPrice TEXT,taxableLocal TEXT,taxableState TEXT,taxableFed TEXT,itemManufacturer TEXT,Catagory1 TEXT,Catagory2 TEXT,Catagory3 TEXT,itemSerialNum TEXT,itemNotes TEXT,"&\
      "localTaxAmpunt TEXT,stateTaxAmount TEXT,fedTaxAmount TEXT, FOREIGN KEY (theCount) REFERENCES repairReceipts(itemQuantity),FOREIGN KEY (theCount) REFERENCES salesReceipts(itemQuantity))" into tMyFiguresStatement3
      revExecuteSQL sDatabaseID,tMyFiguresStatement3
      
      if the result is not an integer then
         answer "Error creating inventory table" && the result
      else
         answer information the result && "inventory table created"
      end if
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 » Sat Apr 09, 2016 12:43 am

OK, that makes things clearer:

Inventory table = one row per inventory item
salesReceipts = one row per sale per item
repairReceipts = one row per return per item

What I don't see is a table for items booked into inventory, either as a result of buying them or manufactured by the company. Or maybe I'm missing something? Without that last table (or something equivalent), I don't see how you can keep an accurate count of your inventory.

Leaving that aside for now, it sounds like you are still thinking about maintaining the count per inventory item either manually or programmatically, which brings us back to my original post on this thread. The best way to get a current inventory count is as explained in my previous posts bu a SELECT statement that sums the total of incoming inventory and subtracts the total of outgoing inventory. The latter would come from your salesReceipts table but as mentioned above, not sure where the incoming inventory comes from.

Pete

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

Re: The infamous POS app

Post by quailcreek » Fri Apr 15, 2016 2:54 am

I think I'm starting to get this worked out. These are the 4 tables I'm working on along with the user interface to make them work. I just thought I'd see if anybody has any thoughts on how I have the tables designed before I get too far down the wrong path. Thanks a lot for the help.

One thing I'm not too crazy about is I'm manually inserting the barcode and quantities into the inventory table when a new item is inserted into the purchasedItems table. Is there a better way to do this?

Code: Select all

########################
   ## This is a header table for keeping track of sales receipts. The receiptNumber is defined in the app.
   put "CREATE TABLE salesReceiptsHeader(headerID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,receiptNum TEXT NOT NULL UNIQUE,CustID TEXT,transactionDate Date,"&\
   "PaymentMethod TEXT,salesPerson TEXT,"&\
   "FOREIGN KEY (CustID) REFERENCES Customers(CustID),"&\
   "FOREIGN KEY (salesPerson) REFERENCES Employees(employeeID))" into tSalesHeaderStatement
   
   ## This is a table for keeping sales receipt details. ReceiptNum is the same as the Header table.
   put "CREATE TABLE salesReceiptsDetails(receiptID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,receiptNum TEXT,lineNumber TEXT,itemSerialNum TEXT,itemBarcode INTEGER,"&\
   "itemQuantitySold TEXT,itemPrice TEXT,localTaxPaid TEXT,stateTaxPaid TEXT,fedTaxPaid TEXT,"&\ -- into tSalesReceiptsStatement
   "FOREIGN KEY (itemBarcode) REFERENCES purchasedItems(itemBarcode))" into tSalesReceiptsStatement
   ########################
   
   ########################
   ## Create table items and tracking purchased items
   put "CREATE TABLE purchasedItems(itemBarcode INTEGER PRIMARY KEY NOT NULL UNIQUE,distID TEXT,itemDescription TEXT,itemCost TEXT,itemPrice TEXT,taxableLocal TEXT,"&\
   "taxableState TEXT,taxableFed TEXT,itemManufacturer TEXT,Catagory1 TEXT,Catagory2 TEXT,Catagory3 TEXT,itemSerialNum TEXT,itemNotes TEXT,"&\
   "FOREIGN KEY (distID) REFERENCES Distributors(distID))" into tPurchasedItemsStatement
   
   ## Create table inventory itemBarcode are inserted when purchasedItems are inserted
   put "CREATE TABLE inventory(itemBarcode INTEGER PRIMARY KEY NOT NULL UNIQUE,itemQuantity TEXT)" into tinventoryStatement
   ########################
Tom
MacBook Pro OS Mojave 10.14

Post Reply

Return to “Databases”