The complex views are one of the most powerful features of CrossModel.
These views include two or more different table or views, and still they can be inserted or updated like a single table, thanks to the insert-update stored procedures.
The complex views include data from two or more tables or views in a JOIN clause (OUTER or INNER JOIN).
Once the complex view is created, it inherits all the columns and connections of the included tables or views, merging them in a single structure that can be used as a single table schema.
A practical example
In most ERP on the market, the structure of the commercial documents is very similar between different document types.
An order, for example, has a lot of data in common with and invoice (date, client, payment, etc.) but also some specific data that are not found in other documents, for example a delivery date.
In this case, there are usually two types of data structure:
- Separate tables for each document: in this case the connections between different documents can be quite complex, since there are different tables to be connected.
- One single generic table “DOCUMENTS”, in which the document type is identified with a flag: in the case the table must include all the fields that belong to at least one of the documents.s.
The X-Cross document structure
In X-Cross, the ERP developed with CrossModel, the documents are a complex view that includes, in an INNER join, the general data of the document that are in common between the various types, and the specific data of that type of document. In this way we can have the advantage of both solutions with no one of the disadvantages.
Insert – update with stored procedures
In standard SQL, views that include multiple tables cannot be updated.
In CrossModel, each view that is read-write creates in the database insert-update stored procedures.
In the case of complex views, the stored procedures will split the data between the single tables or views that are included in the structure.
Nested complex views
A complex view can include another view that is a camplex view on its own, with unlimited nesting levels.
The insert-update stored procedure will call the stored procedure of the nested view, that will splita tha data into different stored procedures, and so on to the level of the phisical table of the database.