The retail system primarily uses a distributed mesh database. The mesh database is ideal for the applications purpose, but a relational database output is often easier for external reporting and interfacing. The system therefore supports a model of transparent writethrough to SQL tables.

The illustration shows the broad structure of the system. It is showing the "Locations" data, but this layout is similar for all data. The locations table is used throughout for explanatory purposes.

At the top of the illustration, users interact with the locations data via the Retail application, web pages or any application that uses the eLink API. This API offers full read/write access from any authorised node/application in your network

The locations data is stored in the Mesh database. This database includes the ability to stream output to SQL tables. As records are changed in the mesh database, the SQL table is updated. See Mesh Technical Details for more information about the mesh layer.

There are several types of SQL tables you can optionally create. Simple "current" data or more complex time based log of all changes.

Highlighted Point #1 is where you would add more columns to capture additional information, such as colour of building or postal address. See the tab "Storing Data" for more information

Highlighted Point #2 allows you to control what data the mesh layer writes to the SQL tables. The mesh database has large limits on number of columns, and you do not need to define a SQL column for every fact recorded. If the column is present in the SQL table, mesh will maintain it. See "SQL Tables" tab for more information

As SQL tables are often created a normalised form, the mesh layer also maintains a number of Reference Data tables that you can join too

The mesh database is slightly different to classic relational databases. With relational databases you typically create columns ahead of time for storing data in. With the mesh structure, column space is not allocated and any defined column can hold data. This means some structures can be defined to hold thousands of columns of information and the mesh will only utilise what is required.

If columns contain data that is vaguely useful to multiple customers, we prefer to allocate one fixed Field/Column # to store that value. Different sites are free to use that column or not, but having a standardised column definition ensures reports can be written that are useable beyond a single site.

Sometimes however, values need to be stored that are completely site specific. Most mesh tables have space reserved for a few site specific columns, typically around 8 million columns. To create these columns, you need to supply metadata to the mesh layer so it can understand the data. This includes

1. Required. Datatype. Number, string etc.
2. Human desciption of contents for a technical user eg "Bathroom installation date"
3. URL describing this individual field to end users.
4. Required. Universal Domain. Defines what sort of data is being held - eg "A colour", "a price", "phone numer".
5. Value restrictions. Minimum/Maximum values. Pick lists and other assorted controls on data entry.
6. Search importance. Is this field commonly searched by users, like "name" fields, or is it more internal like "key" fields.

Universal Domains

Universal domains describe what is inside a column. This is not a simple description of contents, rather a link defining exactly what values are acceptable. It is similar to reference tables in relational structures, but is able to understand objects that dont cleanly fit, such as "addresses" or "absolute-time". Having universal domains allows the system to dynamically understand how columns should be processed and also which columns can be used to join different table together.

While a database uses scalar fields such as integer, datetime or char(N) to store data, this does not convey meaning. Mesh requires a universal domain for every field (you cannot store data with no meaning). So two columns that might be stored in "int" datatype fields might be linked to UniDoms, Height_cm and weight_kg. It is now clear that these two columns have no direct relationship to each other. Futher, any report that knows how to use Height_cm data may be able to process this column.

A few examples should illustrate how they work.

Person Name

Defines a field that contains a human person name

• Generally expected to be between 1 and 30 characters, but no restrictions on length
• Any value/character is acceptable, but values are strongly skewed towards letters.
• Reference tables "person.name.male" and "person.name.female" can be used as reference lists
• High priority for user searching

Colour

Defines a field that contains a details of a single colour

• Values must come from "gd.art.colour_names"
• Low priority for user searching, unless exact match on colour name (eg if you search for "yellow" it is higher priority than "yell")
• Some additional attributes available (RGB triples, Hex values)

Price

A numeric single price

• Can only contain valid price characters
• Low priority for user searching, unless exact match on value.

Power Supplier

An entity that supplies power/electricity

• Extremely likely to come from reference list, either global lists or site supplied lists.
• Unknown values may be disabled by site preference.
• Low priority for user searching, unless exact match on value.
• Has geographic meaning.

Address

A physical or postal location

• Can be validated against reference lists
• Unknown values are possible but rare if reference lists used
• Medium priority for user searching
• Has number of additional attributes for each value (post code, postal id, street name, suburb, geo position etc)

The output tables and columns in the SQL database follow a specific pattern to ensure that the mesh layer understands where values are to be placed.

Table Names

Tables are named with the following pattern

NNNN (eg Locations)

The raw current view of the information. Edits are applied to rows and deletes cause records to be removed.

Example:

Id	Name	Phone
1	Main Street	123-4567
2	Airport	123-8899

NNNN_History (eg Locations_history)

Reserved to Fieldpine for future use.

Column Names

All column names are allocated by Fieldpine. Creating unknown field names on tables may cause the Mesh layer to stop writing to the table as it cannot fully understand the structure.

We recommend you create a VIEW over the raw tables to provide more user friendly names for columns.

Fnnnn (Aka Technical Name)

Holds the corresponding mesh field value, where possible. The mesh database uses numeric field identifiers, so any field starting with "F" receives a copy of the field value. This may be the literal value or a key to reference data. Data that cannot be stored, such as photographs, will not be stored even if the column is present.

FieldName

Some fields are allocated more friendly "database" names in additon to their "F#" name. Where present these fields are exact aliases for the F# name.

Controlling Input Selection

For tables that use simple lists as reference data, you are able to block or extend the values to suit your site requirements.

For example, one field on a table may be "country", which is linked to the UniDom "Countries of the World". As the column is defined to contain countries, the system automatically knows that countries is a bounded set of values and uses a lookup style of operation.

Mesh Column "Country". What the user sees

«--»

Maps to UniDom "Countries of the World" Defines standard "type of data"

References

Global Reference Set

If you wish to restrict or add to this set, you create a new domain based on Countries of the World.

Mesh Column "Country". What the user sees	«--»	Maps to UniDom "Our Countries of the World" ( Based on Unidom "Countries of the World" ) Defines standard "type of data"
		Your Filter, Restricts which values are visible from global data
		"Countries of the World" Unidom Global Reference Set	Your Reference Set (Optional)

• This technique is very similar in operation to inheritance from C++ and other OO languages.
• You cannot globally change "Countries of the World". You must change each column to use your new Unidom. While you may wish to restrict the list, the values may be referenced in places you do not expect. You might consider restricting countries to only those were you operate, but countries is also used for "customers" and "suppliers" to reference where they come from.
• Your Unidom class must work in the spirit of the intention and not try to subvert the system. If New Zealand is assigned the key "64" and Australia "62", your class must not switch these key values around so that New Zealand becomes the key value "62". The system reserves the right to disable and ignore any Unidoms that it believes are causing misleading data.

Output Tables

Reference Data used in the SQL output tables is typically placed in a common table named "ReferenceData". The reference data is maintained by the Mesh layer to allow you to join values and gain more useable labels for values.

If you prefer not to have joins and would prefer denormalised tables, see the "dereferenced fields" under the "Storing Data" tab.

A table called "UnidomLink" is also maintained. Rows are added to this table describing the Universal Domain information held in each table/column