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"
The 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.
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)
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.
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.
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.
If you wish to add tables to the database for your own purposes, these table names must also start with the letter 'U'.
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.
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.
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'.
- 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
- Microsoft Sql/Server. Enterprise, Standard, Web and Express
- Any ODBC (V3) compliant database. Oracle and DB/2 are known to work but are not reguraly tested by Fieldpine
- Microsoft Access. Deprecated. Limited support for high volume network shared use