A database is a collection of records stored in a computer in a systematic way, such that a computer program can consult it to answer questions. For better retrieval and sorting, each record is usually organised as a set of data elements (facts). The items retrieved in answer to queries become information that can be used to make decisions. The computer program used to manage and query a database is known as a database management system (DBMS). The properties and design of database systems are included in the study of information science.

The central concept of a database is that of a collection of records, or pieces of knowledge. Typically, for a given database, there is a structural description of the type of facts held in that database: this description is known as a schema. (You can view the coins schema using the database enquiry screen in Coins ERP+ – see accompanying documentation).

The schema describes the objects that are represented in the database, and the relationships among them. There are a number of different ways of organising a schema, that is, of modelling the database structure: these are known as database models (or data models).

The model in most common use today is the relational model, which represents all information in the form of multiple related tables each consisting of rows and columns.

A relational database is a database based on the relational model. Strictly speaking the term refers to a specific collection of data but it is invariably employed together with the software used to manage that collection of data. That software is more correctly called a relational database management system, or RDBMS.

An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

On Relational Database Model represents relationships by the use of values common to more than one table.

In the relational model some bit of information was used as a "key", uniquely defining a particular record. When information was being collected about a user, information stored in the optional (or related) tables would be found by searching for this key. For instance, if the login name of a user is unique, addresses and phone numbers for that user would be recorded with the login name as its key.

The table structure of the Coins ERP+ database has been designed to be organised with a direct relation to the business processes and modules of the system. One of the main attributes of this design is the naming convention used on the schema.

Coins ERP+ have aimed to use a naming convention that would make it easy to identify which tables are used by which module. On most cases the first two letters of the table will point to the module code of the system.

Examples:

ap_ Accounts Payable
ar_ Accounts Receivable
cb_ Cash Book
ci_ Central Repository

The table name will also contain a descriptive element, for example ap_invoice is the Accounts Payable invoice table and ap_invdist the table which contains its associated distribution records.

Each table has a three letter ID, this is used as reference throughout coins – and is often used as the prefix of a field name. In the example of ap_invoice this ID is ain, therefore the field name for the Accounts Payable Invoice balance is ain_balance.

To reference a field the syntax is:

{tablename}.{fieldname}
​
For example :
​
ap_invoice.ain_balance

The coins database is based on various levels, the top level being the Central Repository. Information held in the Central Repository is not Coins ERP+ Company specific and is available across the system.

The main pieces of information held in the Central Repository are:

CI Company Information
​PI Project Information
​TI Technical Information
​PP People Information

In addition to the Central Repository, system information such as Users, Functions, Printers are also held at this top level.

SY System
​MS Menus and Functions
​PM Print Manager
​XL Translations and Language
​IB Insurances and Bonds
​MK Marketing

Most data in the Coins ERP+ database is actually held at Coins ERP+ Company level. Even though only one company may be used the company details will need to be used to access the data. In each instance the company number is held on each table in the kco (current logged in company) field.

There are a set of tables which relate directly to company information – configuration table etc, in addition to generic tables such as Batches – these are held in the co module.

CO Company

GL General Ledger
​JC Contract Status Ledger
​CB Cash Book
​AP Accounts Payable
​AR Accounts Receivable
​SC Subcontract Ledger

CS Contract Sales

FM Facilities Management
​SM Valuations (Site Manager)
​SW Small Works

House Builders Modules

BQ Bill of Quantities
​HS House Sales
​LA Land Management
​VP Valuations and Payments
​WF Workflow

Payroll and HR

CR Credit Control
​HR Human Resources
​EX Expenses
​PR Payroll

Plant, Assets and Stock

CM Components
​FA Fixed Assets
​FL Fleet
​PC Plant Control
​SO Sales Orders
​ST Stock

Procurement

PO Procurement

Other Modules :

BP Professional Billing
​CR Credit Control
​DC/DM Document Control / Management
​IB bonds and guarantees

Coins ERP+ has designed its database to match the processes of the industry and therefore the tables mirror the process components. Each of the modules will cover different process and within each module we can find different sub-processes. The organisational structure of the company is also related to the structure of the tables.

Each of these sub-processes are also divided in tables that will hold information down to the lowest level of detail and there will also be tables that will summarise that information at different levels (depending to the process these could be dates, codes etc).

Company Organisational Structure & Contract Structure Example (House Builders):

Cost Transactions Structure Example (House Builders):

In addition to the Summary and detail information, Coins ERP+ also has specific tables for open transactions in the database. This is to enhance performance when maintain and reporting on current data.

For example every PL Invoice that has not been paid, or has been part paid will have an associate record in the PL Invoice Open Item table. Once an invoice has been fully paid the open item record is deleted.

It is therefore recommended that when enquiring or reporting on open items that it is the open item record which is used as the basis of the query.

For example :

Copy

Each of the tables which contain transactional data will have an associate open item table.

Databases can take advantage of indexing to increase their speed (Dataset retrieval using queries). The most common kind of index is a sorted list of the contents of some particular table column, with pointers to the row associated with the value. An index allows a set of table rows matching some criterion to be located quickly.

The order that columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using only the first indexed columns. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.

For example, imagine a phone book that is organised by city first, then by last name, and then by first name. If given the city, you can easily extract the list of all phone numbers for that city. However, in this phone book it would be very tedious to find all the phone numbers for a given last name. You would have to look within each city's section for the entries with that last name.

Each Coins ERP+ table has one or more Indices. An Index is built up of several fields in a record which in combination will assist the query in narrowing down the number of records which will be read to determine which meet the query requirements.

Each Table has a Primary (the index used by default unless you determine otherwise in your query) and a Unique key (the combination of these fields in a single record is always unique).

However Coins ERP+ will use the most appropriate index for your query.

An example of how an index would work is to use the Current Logged in Company (kco) in addition to Contract Number (job_num) to search for a particular contract. Another would be to use Current Logged in Company (kco) plus Order Type (tip_type) where you would query only where tip_type = "TRADE", the query would immediately know only to search through Subcontract Orders to find orders which matched the other criteria rather than search every single order.

Every table in the coins database has a RSP (Record Service Procedure). Each of these RSP's provides the Coins ERP+ Reporting tool with the business logic required to extract the appropriate data from the database.

In the Database Enquiry you can see the RSP under the Table Code and its Label. The RSP's have a naming convention –

{table-ID}-rsp.p.

Where the Table ID is as shown in the Database Enquiry (you may also hear this referred to as the Table Acronym or TLA).

RSP’s control, amongst other things, the basic table update functions for that table. Each RSP has a common set of methods that define standard behaviour for the object. They control record scope and locking, security, default values on creation, data integrity rules etc.

The RSP can also extend the database table to produce a logical view of data for the business logic to work with. A by-product of this is that we are to provide an XML field on every record in the database in which the user can configure their own extended fields for use in the presentation layer. This enables client-specific fields to be added to screens and included on reports. The RSP handles the translation of the data from the logical record buffer to the physical fields in the physical database.

The RSP is also able to de-normalise the database for the purposes of logical data access. For example, the contract record (jc_job) in the database is linked to many other tables including the contract group table (jc_cgroup). To show the group description on a screen or report it might be expected that the interface designer would have to build a query to link the contract record with the appropriate group record and then display the group description from that record. The RSP extends (for read-only purposes) the table and de-normalises the data and makes the contract group description available as just another field on the contract table.

The data remains physically in the jc_cgroup table in the database, but to the business logic and presentation layers it is shown in a more logical place which is on the contract record.

The same principle is applied to more complex calculation fields. For example, the value of accruals on a contract is a highly complex calculation involving many tables (purchase orders, order lines, goods received notes, etc). Again, this data is made available in the logic buffer in the RSP so that to the interface designer it is just another field on the contract record. They are simply able to paste, say, the contract number, contract name, group description, and accrual value on to a page without any need to know where each of the bits of data is coming from.

The RSP is also able to simplify the database for updates.

There are instances where common database tables are reused in many areas of the application. An example is the address table sy_address. This holds the address details for an employee record in the HR system. In this instance the RSP is able to link the two records together, presenting a single logical table to the business logic and presentation layers. The updates are performed on this logical buffer in the RSP and it is only the RSP that knows that the data is split into two separate records when written back to the database.

The RSP also has an audit layer so that as the logical record is committed back to the database, changes on the logical table can be recorded in the audit records. Auditing can be performed as part of the managed data source through the use of triggers, but in this instance the audit records created are an audit of the physical data and it is much more difficult to reconstruct the separate physical table and field audit records in to a logical view of the record at a later stage.

The RSP controls all data access through to the database. Coins ERP+ can insert bespoke trigger code in that RSPs that can act on data as it is committed back to the database. For example, this can be used to push changes in business data in Coins ERP+ out to a data warehouse application by creating XML messages from Coins ERP+ that are then consumed by a data warehouse load interface. In this way it is possible to keep a data warehouse up to date with live business data.

In addition to the standard tables and fields in the coins database, Open Architecture also uses the RSP's to provide access to certain calculated and non standard fields. These are known as "RO" or Read Only fields and are also fully documented in the Database Enquiry.

Although these fields have certain restrictions, they are incredibly powerful when used in enquiries and reports.

In most instances RO_ fields will provide information from related tables to the main queried table – for example summary cost information at Contract Level, or descriptions from an associated Lookup Table without the Page or Report designer having to query and access many tables from the coins database.

Many of the calculated fields reflect similar fields to the coins + Configurable Reporter, such as Accruals, Costs and Revenue fields. These fields can then be passed parameters to enhance the information returned to a report. Typically these fields can be limited by dates, values and financial periods as well simply parameters such as “TD" for a To Date value.

In the Database Enquiry RO_ fields are shown in a format as the example below. Any parameters immediately after the caret are mandatory; each parameter is then separated by a pipe. Any parameters which are encapsulated in square brackets are optional.

RO_ContractCosts^<PeriodType>[|<PeriodOffset>[|<FDate>[|PhaseMasks[|CostcodeMasks[|CategoryMasks[|AnalysisMask]]]]]

Many RO fields allow for period selection with a period offset

Period offsets have 3 options

- a relative offset (number of periods forward or back), e.g.

For CVRs these work slightly differently to other parts of Coins ERP+ as we use the CVR periods to calculate the offset and there may be less of these than GL periods. E.g. we may have CVR periods for jan, mar, may, jul (every 2 months). In a May CVR the use of -1 offset would return March

- period number with a year offset, like this:

The benefit of using this method is the fields will be selectable by the user as report columns when building the report, however as RO Fields they will NOT be available for sorting and grouping.

The disadvantage is that the fields will be hard-coded to the state they are exposed as and any change to their functionality must be made by the administrator and cannot be changed by the report writer users. For this reason, it is recommended that RO fields are used in calculations within the Report rather than by Exposing.

Go to the Coins ERP+ Reporting and BI Setup and Table Documentation and select the table in which the field you wish to pass parameters to.

The Parametrised Fields section then needs to be populated with the appropriate RO_ field and any parameters that are required. (The parameter information can be found in the Database Enquiry.

A single field can be documented more than once with different parameters, for each a balance field can be documented with several dates, such as a year of period end dates.

Examples of an RO_ field and associated parameters are :

jc_job.RO_ContractCosts^TP|-3 (This Period with period offset -3)

cs_certificate.RO_cst_ctd_cum^10 (Contract Sales Certificate Item – Cumulative to Date Certificate Line 10)

Once a RO_ field has been entered in the Table Documentation with appropriate parameters it will then become available in Report Writer for selection as per standard database fields.

Lookups apply to normal (fill in) fields, and to 'disabled with lookup' fields. Fields for which a lookup has been set up have a lookup buttonnext to them; disabled with lookup fields have lookup and clear buttons next to them:

The lookup button launches a separate browser, from which the user can select a record. The lookup then returns values to populate one or more fields on the form. If a user adds a new record whilst in a lookup (assuming this is allowed) then when they save the new record it will automatically be selected and returned from the lookup.

In Lookup Maintenance, enter a record for the field to provide a lookup for.

In the Lookup Function field enter the function to call for lookup. This function is a normal browse/list type page (and could even be say the maintenance function - the maintenance buttons will be retained if access security permits). Links are removed.

To provide different lookups for the same field on different functions, enter separate records with the function code in the Function field. If the Function field is blank, Coins ERP+ uses this as the default lookup for the field.

The inline lookups are built using the existing page for the lookup button but there are two new options you can specify in the parameters of the lookup. quickFields and quickShow.

By default the inline lookup uses the same field (keycode/lookupcode) that is used by the lookup page and does a begins on that field. If you need to modify the field to another one or multiple then you can do this with quickFields. This is a comma separated list of field names to be matched when typing in the characters in the fill in. Example is poh_attention where quickFields is set to ppc_name,ppc_surname so the matching is on either of those fields.

In the parameter field for the lookup, it is possible to specify the following: