Physical Database Documentation
 
Library Physical Database

Commonly Used Tables

Products Salelines Sales User Data Accounts Contactlog Eftposreceipts Eftposreceipts2 Locations Payments Pricemaps Reference Data Statetable System Data R_paymenttypes Salelog Usagetoken Analytics Data Ds_customer_a Ds_customer_b Ds_product_a Ds_sales_a Other Tables Accountadjustments Accountallocations Accountchangeslog Accountshumanrequired Accountsupport Activitylog Advisor_actions Advisor_tests Allocatedrights Apikeys Apikeys_access Apikeys_lock Apikeys_resource_locks Apitxnlog Apitxnlogdata Asndetail Asnheader Asnheader2 Asnitem Attribute Attributedefinitions Authcodelog Autobalancerecon Autobalancereconlog Backgroundlog Barcode_patterns Barcodefailurelog Barcodeinvalid Barcodeoneoff Barcodeoneofflog Barcodepatterns Barcodes Barcodes_ean99 Barcodes_trade Barcodeusedlog Bisho_status Bistx Bookingres Bookingsales Bookingscans Bookingsessions Bulkloadlog Cabinets Callerid Cameradef Carriers Cashdraweractionlog Cashdrawerlog Cautionlist Ceres_productlist Chatheader Chatmessage Companies Configui Contact Contactdetails Contactdetailsmap Costchangesseen Counters Counters_log Createapps Customercardgroups Customercards Customermessages Customers Customersdcid Customersdcidrpt Customersdcids Customersdcidslinks Customerstate Customertracks Customertrackspending Decisiontreeheader Decisiontreelines Demographic Discountfilter Discounts Discountsmanual Docuheader Doculine Documentformats Documentparsematch Documentprocessing Documulti Doesnotexist Drivercashlog Ediauditlog Employees Eventaudit Expectedsales Expensetypes Faults_detail Fdlonline_enablematrix Feectl Fees Floatlog Flow_anchor_raw_string Fmea Fpuicontrol Fpuicontrol2 Franchise_barcodes Franchise_departments Franchise_products Franchise_products_original Futuredatachanges Futuretxns Gateways Geoareas Globalvalues Goodsinheader Goodsinhistory Goodszeropoint Headerreceive Headerreceive_head Heartbeat Hiregroup Httpurllog Idset2 Idsetctl Idsetdata Idsetdata2 Inventorylevels Ldflog Linkedserver Livepayments Livesalelines Livesales Localsaleflags Location_area Location_cab Location_tradingdays Longdatavalues Machinesuser Macro Maintlist Marketingprogram Measurements Media Mediacontrol Mediafiles Mediaresource Mediastorage Membership Membershipdata_all Messageformat Messageq Messageuser Messagingt Modifiergroups Modifierheaders Modifieritems Modifierpriceheader Modifierpriceline Modifierproductgroups Modifieruplifts Modififergroups Multichain_sourcemap Multilane Multilink_all Multiplu Networklane Networklane_environment Networklane_status Networklanecode Networklaneconfig Ordercategories Ordersreceived Ordersreceivedlines Orderstxns Overflow_gnap Overflow_tmpr Perfcounters Pidcoststatus Plicence Poheader Poheader_head Poheaderreceive Poheaderreceive_head Poline Poline_head Poms Posaudit Posendtypes Poshipping Poslat Posrelationships Prefsaleitems Pricebandmap Pricebands Pricebookcurrent Pricebooksource Pricebookspecialsheader Pricebookspecialsline Pricebufferdist Pricechangepending Pricegroups Pricehistory Pricemapsexceptions Priceoptions Pricerevision Priceused Printformats Printing_pending Printingformats Printingrules Procuserlogicline Product_cab_active Product_dispatch Product_dispatch_error Product_packsize Productattribute Productconnections Productconnectionstype Productcontrollog Productkitdef Productkits Productkitsext Productlocspacemap Productlocspacesets Products1 Products2 Products3 Products5 Products_category0 Products_category1 Products_costhistory Products_inventory Products_inventory_changes Products_inventory_point Products_manufacturer Products_overlay Products_properties Productsbom Productsbomheader Productsinst Productslocation Productslocationbis Productsordering Productspacesets Productssale Productsupplier Productsupplierdist Productvariant Profanity Purchasecontrols Purchaseorderprocessing Qoh_pub Qtydiscounts Quickcodeaudit R_colors R_departments R_departments2 R_departments3 R_departments4 R_departments5 R_departments6 R_departments7 R_departments8 R_fashiongroups R_pgm_periods R_returnreasons R_sizes R_styles R_writeoffs Rcvstock Rcvstockline Receiptextn Remotecommands Remotecommandslog Rewardmovements Rewardprogramoptions Rewardprogrampids Rewardprograms Rewardprogramspids Rewardtxns Rewardtxnslog Sale_delivery Salealloc Salechangesaftercomplete Salechar Salecomments2 Saleinfo Salelines_cost Salelines_deleted Saleloadtrace Saleloadtraceline Sales_errors Sales_picking Sales_recall Sales_standingorders Salesdcid Saleservercommands Salesprofilesingle Salestaff Salestatistics Salesunpaid Salevar Salevar2 Samplelist Scriptmap Scripts Seritem Seritemlog Servercommands Settings Settingsactive Settingsdist Settingskv Settingskvdist Settingsmembership Settingspolicy Settingspolicymap Settlecashdetail Settlepaydetail Shelfvisit Shifttrack Ss_pricebuffer Staff Staff_eauth Staff_magid Staff_ownership Staff_rights Staff_stats Stafftablecontrol Staffused Staffused_log Statementhistory Stickies Stock_fifo Stock_lifo Stockcount Stockholdrequests Stockmovements Stockpoints Stockreceipts Stocktakedetail Stocktakedetail2 Stocktakemaster Stocktakesnapshot Stocktakesnapshot_tracked Stocktxnhdr Stocktxnitem Subscriptions Supplier_inventory Suppliermessages Supplierpricebook Supplierpricebookline Suppliers Suppliers2 Suppliersautoorder Svcaccount Svcards Svctxnhistory2 System_status Tabledisthead Tablemgmt Taxrates Telephone_number_plans_override Ticketpricerequired Ticketpriceshowing Timeclock Timeclocklog Timeschedule Tmpr_log Tnotes Topology Tracelogic Trackeditem Trackeditemobservation Traffic_counts Transit_inventory Transit_inventory_lines Transit_request Transit_request_lines Triggers Tushlog Typesuser U_cw_cartridges U_cw_printers Upstream Usagerecord Userlogicvariable Valuedomainrule Vehicle_makes Vehicle_models Versioncontrol Verticalsecurity Voucher Voucherprogram Webgroup Webhooks Webpages Webpagesrules Worklist Workqueue Workqueuetx Internal Technical Tables B2backup Customers2 Customers3 Dbcomm Emailinrules Firewallrules Keylease Keyleasefreelist Localdb_datpq Localdb_datpq_inbound Localdb_lanetx_queue Localdb_membership_replicate Localdb_pinb_cache Localdb_receivequeue Localdb_replicationstatus Localdb_salestechnicalaudit Localdb_sendqueue Localdb_sendqueuedata Localdb_settings Localdb_values_write Localdb_version Loginkeys Products4 Replicationcontrol Salememory Sales2 Serverprocessing Serverprocessingdates Tubtlocal Wipheader Wipline Deprecated Tables Auditsystem Pictures Retired Tables Biscontrol Cjl Eftpos_txns Linkproductspictures Pomprodlink Experimental Tables Keytranslate Posscriptpage Posscripttrigger

Physical Database Details

The database in Fieldpine POS is central to the complete system. While the structure is open to see and some parts documented there are a number of rules that must be followed in order to ensure system stability, now and in the future. The rules basically boil down to "play nice, and don't risk causing harm to the POS"

StructureThe Point of Sale has both a physical database layer (what you see in the database) and a logical layer (what the various APIs deal with).

If you are interfacing, you should in general be using the API interface (highlighted #1 on the picture) rather than the database. This is especially true if you wish to write or edit records, as only the API ensures accuracy, validation and audit tracking. Of course, creating high volume reports and some other functions are often easier with SQL database engines, so the physical structure is documented.

One of the main advantages of this seperation between API and physical database is it allows changes to the physical database over time without having to re-engineer customer specific interfaces. With this approach, tables and fields can be altered without affecting the APIs used by the POS itself and external applications

The rest of this document is talking about the physical SQL table layer (Highlighted #2 on the picture)


There are two types of tables in the SQL database, "physical" or internal tables which are the actual data used by the POS itself. These are the tables you should try and avoid. There are also "readonly" or "public" tables which contain extracts of live data for use in your external systems. You are strongly advised to use the readonly tables wherever possible.

Failure to follow these rules will result in no support, or charges for time spent.

  1. You are free to read the database as you see fit. You should however not configure high volume reading applications that would starve the system of resource for POS processing. Remember that you are solely responsible for ensuring accuracy of what you read. Reading the database directly is a low level interface and you need to handle all special cases, especially flags and row controls. The system also utilises pending transaction type tables, so facts might be not fully complete if pending transactions still exist. Some fields may be stored using encryption and/or obfustification techniques (these are documented when used)

  2. Do not write (meaning insert, update or delete) any field or table unless it is your own field or table. There are exceptions to this rule for specific cases. This rule is not meant to stop functionality and system integration, it is to ensure that undocumented updating does not occur.

    Any table that is replicated, regardless of its the name (ie, this applies to customer table names that start with the letter 'U') should not be altered in any way except using approved applications (Database utilities, ODBC and SQL are NOT approved applications). Fieldpine Systems use a distributed temporal database model, typically stored in either a relational backend or direct files. The side effect of this is that reading data directly from the underlying database may not be cache-coherent at the instant it is read or changed, and changes may be overwritten without warning by approved applications.

    The underlying transactional databases are physical representations of data, not logical facts.

    Fieldpine Systems are also increasingly required to conform to regulations around auditing, personal information security and finance reporting. (eg Tax Law, Personal data protection, Sarbanes-Oxley, Credit card security standards etc) Direct writing to the database without correct audit trails often violates these rules.

  3. The central POS servers are architected around being able to be shutdown for short periods of time (even during the day) without overly impacting trading operations. Do not design external applications that would not permit these shutdowns, they make operations and support much easier.

  4. If you wish to add columns to database tables, the names must start with the letter 'U' (for user). Keep in mind that Fieldpine POS is a distributed database system and remote databases may not be the same type or even from the same vendor, so not all datatypes may be available. Do not add more than around 50 columns to POS tables without written approval. See Fields for more information on field naming.

  5. If you wish to add tables to the database for your own purposes, these table names must also start with the letter 'U'.

  6. Do not use the "bit" storage method present in some databases. Define the field as a integer and use the boolean operator in POSMETADEF files to instruct the POS it is a boolean field. Do not use any other vendor specific datatypes for any column.

  7. Do not create unique indexes on column groups you believe to be unique, unless it is documented that it will be unique for all time. This also applies to triggers, constraints and other database level controls. If the tables are site specific tables that you have created you may of course define whatever indexing you wish on those tables.

    You may add indexes to support reporting performance as necessary.

  8. While you are free to create triggers and database external functions, you should try and avoid creating these that require remote systems to be functional. The intent here is to minimise the external system dependencies which would impact the POS. As Fieldpine do not use triggers within the POS (due to varying levels of support in different database engines); naming restrictions on triggers and functions only reserve certain prefixs of: 'trig' 'gds' 'gnap' 'fpos' and 'fdl'.

  9. This documentation of database structure is supplied as-is. Fieldpine reserve the right to change or add anything at any time, without warning.

Supported Database Engines

The following database engines are supported

  1. Microsoft Sql/Server. Enterprise, Standard, Web and Express
  2. Any ODBC (V3) compliant database. Oracle and DB/2 are known to work but are not reguraly tested by Fieldpine
  3. SQLite
  4. Microsoft Access. Deprecated. Limited support for high volume network shared use