The data structure of X-Cross makes full use of the advanced features of the modern databases:
- Transactions
- Referential integrity
- Stored procedures
- Triggers
- Views
for the best possible operating speed and reliability of the data structure.
- The modern databases offer a set of funcionalities that, all together, make the data structure respect the so called “ACID” criteria:
- Atomicity
- Consistency
- Isolation
- Durability
For an explanation of ACID see: https://en.wikipedia.org/wiki/ACID.
To meet this criteria it is not sufficent that the necessary features are present in the database, but also the ERP must make full use of them. For example, if the database offers the feature of referential integrity, but the program doesn’t use it, the ACID criteria are not satisfied..
In X-Cross the ACID requirements of the database are satisfied making full use of the features that are available.
Transactions in insert-update-delete
In X-Cross, all the insert and update of records are included in a single transaction.
For example, the insert- edit of an accounting transaction is a single database transaction. This structure ensures that either all the inserts-updated are successful (“COMMIT”), or else the whole transaction is reversed (“ROLLBACK”), and in this case the data remain unchanged.
Transactions in complex objects
The X-Cross data structure is often based on complex objects:
For example, an invoice is formed of many tables (header, lines, lots, serial numbers, commissions, charges, etc) that all together form the “invoice” object.
In X-Cross, the insert and update of all the tables that form an invoice is contained in a single transaction, that includes all the tables and subtables, at any level, in a single transaction.
In this way, the transaction is not limited to the single table (header, lines, etc.) but to all the tables that together form the object, that is seen as an “atomic” entity.
This is valid not only for an invoice, but alsto for all the objects that are formed by more than one table.
Transactions in a single SQL statement
A very important feature of X-Cross is that the insert or updates of complex objects including multiple tables is made not only in a single transaction, but also in a single SQL statement.
In many programs, in fact, even if the multiple inserts-updates are included in a single transaction, the various operations are performed with separate statments.
If something goes wrong, the client has to take notice of the problem and perform the appropriate action (ROLLBACK), and there is not a total guarantee that this happens. Moreover, these actions are performed in different times, so in the meanwhile the transactions is “hanging”.
In X-Cross, instead, the transaction is always a single SQL statement
thet is automaticalli reversed (ROLLBACK) if something, at any level, goes wrong. These levels can include indirect actions, like triggers that are fired by insert-edit actions.
Referential integrity
In X-Cross, all the connections between tables create a foreign key in the database, with a full referential integrity that ensures the consistency of the data.
The foreign key prevents the user from deleteting a record that is connected to another one: for example, a client that has invoices cannot be deleted.
Cascade in foreign keys
In other cases, the foreign keys can delete the child records when the parent one is deleted. For example, deleting an invoice header automatically deletes all the invoice lines as well.
Consistency of the database – no “orphans”
In databases that don’t implement referential integrity, it is possibile to have “orphan” records, i.e. records connected to a parent that does not exist any more, for example invoices without client, or invoice lines without header.
This can happen because the delete of the single tables are not included in a single transaction, so if something goes wrong the data can become inconsistent.
The referential integrity, with or without cascade, prevents the database from creating “orphans” record, and ensures consistency.
- Without cascade, the parent cannot be deleted if there are child records connected
- With cascade, the child records are deleted together with the parent
- In both cases, the database integrity is ensured.
Stored procedures
All the operations of insert-edit-delete are performed via stored procedures, ensuring the maximum performance and, at the same time, enforcing the consistency and isolation of the database.
The stored procedures are used also to read the data in multiple obects (e.g an accounting transaction, and invoice, etc.) that include multiple tables, increasing the speed of the reading in a dramatic way.
This is very important in X-Cross, whose complex objects can include many different tables or views (each one of them can include many different tables)
An accounting transaction object, for example, has 28 different views to read (header and 27 subtables)
- The accounting header is a view, composed of 42 different tables
- Each accounting line (one of the 27 subtables) is composed of 32 differenrt tables.
A client order object has 30 different views to read (header and 29 subtables)
- The order header is a view, composed of 62 different tables
- Each order line (one of the 29 subtables) is composed of 70 differenrt tables.
To read an obkect like an order, with 29 subtables, would require quite a long time; in tha program needs 0.2 seconds per subtable, the total reading time will be around 6 seconds.
The stored procedure technology of X-Cross can read such an object in 0.2-0.3 seconds, that is around 20 times faster.
Triggers
Many updates that are consequence of other insert-edit (e.g. update of the stock inserting or updating a stock transaction), and in this case they are modified vith a trigger, thet is a procedure that is fired as a consequence of another insert-update-delete.
In this way the secondary update is not perfomed before or afer tha main transaction, but exacly during the transaction itself. If the transaction is reverse, the databse remeing toally unchanged.
Views
The CrossModel data modelr makes full use of the views, that are used for many different purposes:
- Aliases of the tables for connections to multiple records of the same table
- Complex views with two or more tables in the same view, that can nevertheòess be seen and inserte-updated as a single table
- Views (queries) including all the connected records of a main table (e.g. an invoice that is connected to the client, payment term, etc.)