In Pricefx, all persisted data are stored in relational databases. There are two major categories of data — master data and analytical data. These are stored in two different databases, and are accessed in different ways. This is partly due to performance considerations; since they are optimized for different use-cases. But there are also historical reasons behind the differences.

Master Data

Most data is classified as master data. A few examples are products, customers, quotes, price lists, users, and dashboards. The master data tables tend not to be nearly as large as analytical data, and they also tend to be queried more frequently. For example, when a user enters the quote main page, multiple master data queries are sent out to fetch quotes, quote folders, and preferences. For this reason, master data is persisted in a relational database that optimizes the performance for this use-case.

Each type of master data is stored in a pre-defined table in a relational database. It is not possible to access these underlying databases directly — all read and write operations have to go via the API. It is thus not possible to create or modify your own master data tables within the database.

However, there are other features that allow you to customize properties (see next section) and to create other kinds of higher-level tables (see lookup tables and extension tables).

Custom Properties — Attribute Metadata

Some tables have customizable properties. Each field has a schematic representation that is called attribute metadata. This object describes various properties that can be customized. Some properties are:

fieldName – The name of the column in the relational database. Use this to refer to a field within any of the APIs.

label – A string that uniquely identified a field within the master data type. Since it must be unique, you can use it within the APIs to refer to a unique field.

labelTranslations – A JSON object that maps language codes into labels that will be displayed on the screen for the end-user. If this value is omitted, the label will be used as default.

fieldType – An integer that identifies which primitive type the data belongs to. It is not possible to define the type of the field directly in the relational database, so all values are internally stored as strings. When the fieldType value has been set, the string value will be converted into the type that is identified by the fieldType.

Some properties are customizable, but they have pre-defined meaning and are expected to be used in a specific way. For example, in the Product master table; the sku field uniquely identifies a product, while the lastUpdateDate tells the date at which the product entry was last updated. You will not be able to change the fieldType of such properties, but you will be able to change other properties. Both the frontend application and the backend application will make use of these properties in various ways. For example, the sku will be used to assign products to price lists.

When the fieldName starts with attribute followed by a number — for example attribute3 — this indicates that the property is truly customizable and can be used for whatever purpose. The frontend application will make no assumption about such property, so it is up to your custom logic to interpret the meaning and make use.

Custom Master Data Tables

Although you cannot directly modify the master data tables that are persisted in the relational database, there is another mechanism by which you can create master data entities that behave like tables. See the articles on extension tables and lookup tables.

Analytic Data

Some tables can become very large. The typical use case is transactional data, where a single table can contain millions of rows. These tables are queried less often than master data, but the queries are more time-consuming. Because of this, they are stored in a different database that optimizes for this use-case.

Preferences

For any table, the end users can reorganize the columns, sort the rows, apply filters. These settings can be persisted by being saved into preferences.

For any single table, there can be multiple preferences; and the users can switch between them. A preference can be either personal or global. A global preference is accesible by all users.

If a preference is marked as default, it will be automatically applied whenever the user loads that table.

Preference can be persisted in Git — and deployed remotely. This is useful when you want to change the default layout for a table for all users.