Self-hosted inventory: Example Access Database Schema

There are four essential tables:

• SKU
• Transactions
• UPC
• Version

The database is placed in a directory monitored by the Windows service, and the SKU, UPC, and Version tables immediately loaded into memory. Item data is now available to the mobile device using the Android app over the internet and will be completely downloaded to the device. 1 GB is typically transferred in a few seconds over WiFi; the mobile app can then function fully offline if necessary. The transactions table in the PC database will receive scanned inventory data from the mobile when an inventory upload is triggered.

SKU

Contains basic item description and price fields accessed via the SKU field.

Transactions

This table is updated with the mobile device recorded inventory.

UPC

Cross reference between UPC and SKU. This means that barcodes coded by your SKU or UPC can be scanned successfully. Any kind of symbology coding can be handled.

Version

Any string will do as a version identifier for your database. The mobile device will compare its current database version with the one read from the server and alert if different. The version is also used to trigger a mobile device database update at a predefined time, say after midnight etc.