In the CCTS design, a business information entity (BIE) describes in a business context some abstract concept. The aggregate BIE (ABIE) is an abstract structure that contains two kinds of children: a basic BIE (BBIE) is an expressed value and an association BIE (ASBIE) is a concrete instance of an ABIE found in a CCTS document.

In an aggregate every child BIE must have a cardinality of one of the four possibilities: mandatory, optional, mandatory and repeatable, or optional and repeatable.

The data type of an ASBIE is an ABIE, and not necessarily an ABIE of the same name as the ASBIE. For example, the ASBIE JurisdictionRegionalAddress is an instance of the Address ABIE.

While a BBIE appears to be simply an expressed value, it in fact is a composite value. The basic value is expressed as an instance of a core component data type. For example, the BBIE InstructionID is an instance of the Identifier core component data type. There are supplementary components for every core component that may be, and in some cases must be, expressed in addition to the basic value. Consider the "Amount" core component data type described at http://docs.oasis-open.org/ubl/cs1-UBL-2.1/mod/summary/reports/UBL-AllDocuments-2.1.html#UDT (where all unqualified data types are listed and where they are used is indicated by the hyperlinked left angle brackets). There is one mandatory supplementary component named currencyID and one optional supplementary component named currencyCodeListVersionID. One can see at that link that only date, time and boolean values do not have supplementary components, while all others do.

Note there are two kinds of ABIE: the document ABIE (which stands alone and is not currently ever referenced as an ASBIE, but may be in the future) and the library ABIE (which never stands alone and is always referenced by an ASBIE). To accommodate possible future references to a document ABIE, both the document ABIE and the library ABIE are modeled the same in this proposal.

The proposed essence of accommodating both cardinality and complex data types is to map every XML element in the UBL document into a set of pointers into rows. An ABIE row has pointers for every BBIE and ASBIE in that ABIE. Every abstract data type (both BBIE and ABIE) has an SQL table. Every element in the UBL document represents a row in a data type's SQL table. The ABIE is therefore only a set of reference values and the data type tables dereference that value into the set of objects for the parent.

Consider this example shown in Figure 1, “Table references”, though only slightly modified from reality to illustrate the concepts. Each table has reference ("Ref") and sequence ("Seq") columns. The reference values are unique identifiers of some kind, quickly looked up in a column, and not unique. The sequence column has numbers and are not unique. The combination of reference and sequence columns is unique across all rows in a table. The combination need not be unique across the entire database but may be so if the implementation is faster because of that choice.

An invoice instance begins by populating a new row in the Invoice ABIE table. This row describes all children of the <in:Invoice> element. Being a document ABIE, the reference and sequence columns are null.

Figure 1. Table references

The invoice child <cbc:ID> element creates the reference A to the Identifier Data Type table. A row in that table is created with the reference A and the sequence number 1. The element's value is put in the value column ("Core") and any attributes that may have been found in the element populate the corresponding columns in that table.

Consider for illustration only that the cardinality of <cbc:IssueDate> is "1..n" and not "1" and the input instance has two such date elements. The one Issue Date column is assigned the reference B and two rows are created in the corresponding Date Data Type table, distinguished in their order by the sequence values 1 and 2. Note there are no supplementary components on date values and so no attributes are expected to be found.

Another child of the invoice is <cbc:DueDate> and it creates a Due Date column value of reference C, and as there is only one there is only one row created in the table with that reference and the sequence number 1.

The invoice child <cac:PrepaidPayment> creates the reference D and a row in the Payment ABIE table (note the ABIE name is different than the ASBIE name), and as there is only one there is only one row created in the table with that reference and the sequence number 1. The payment columns are shown here: http://docs.oasis-open.org/ubl/cs1-UBL-2.1/mod/summary/reports/UBL-AllDocuments-2.1.html#Table_Payment.Details. Note the diagram shows ASBIE columns for illustration of other library ABIE constructs as the payment ABIE doesn't actually happen to have any ASBIE.

The prepaid payment child <cbc:PaidDate> element creates the reference E to the Date Data Type table. A row in that table is created with the reference E and the sequence number 1. The element's value is put in the value column ("Core").

The prepaid payment child <cbc:InstructionID> element creates the reference F to the Identifier Data Type table. A row in that table is created with the reference F and the sequence number 1. The element's value is put in the value column ("Core") and any attributes that may have been found in the element populate the corresponding columns in that table.

Many UBL elements are optional. When an ABIE row is being populated, all columns of absent child elements are set to null.

Many UBL elements are repeatable. When an ABIE row is being populated, only a single column for the repeated child is populated, that being with the reference to the ASBIE or data type of that child. As many rows in the applicable ABIE or data type table are populated for each of the repeated elements. Each row is assigned the ordinal sequence number.

An ABIE table has the reference and sequence columns, then as many BBIE and ASBIE columns as required for one of all possible children for an instance of the ABIE.

A data type table has the reference, sequence and core value columns, then as many supplementary value columns as supplementary components are defined for the core component type. The BBIE element value goes in the core value column and the BBIE attribute values go into their corresponding supplementary component column. Absent attributes are represented with null values in the corresponding column.

The files generated in this package represent SQL table definitions for UBL 2.1 CS1 documents available at http://docs.oasis-open.org/ubl/cs1-UBL-2.1/ .

Version with comments: ubl-20131013-0020z-commented.sql

Version without comments: ubl-20131013-0020z.sql

In this proposal, all BBIE field and values are stored in the corresponding unqualified data type table. Thus, constraints imposed on the unqualified data type table are imposed on all instances of that type. In XML there is no maximum length or value of any BBIE. If SQL mandates a maximum length of a field, that maximum length is for all values of the data type for the table.

This can be finessed later on by creating qualified data type tables and cherry-picking which BBIE elements are associated with which qualified data type. While the schemas do not constrain the values of a BBIE, the second pass validation step in UBL validation can be modified to check such constraints. See http://docs.oasis-open.org/ubl/cs1-UBL-2.1/UBL-2.1.html#CODELISTS.

Note that the binary object unqualified data type could be very large. Though it is made up of only text characters (in base64 encoding), it is opaque and could be treated as a blob.

Assuming the integrity of party identifiers (at a business level), would it be possible to identify that after loading the database with the information from an instance it is a copy of an existing item? Doing so, would there then be only one instance of the party expressed in the database?

In fact this might not be desirable because if one changes the party information for the generated invoice for a corresponding order, the party information in the corresponding order would change and not reflect the true data of the order.

The one (so far) standardized extension for signatures can be modeled the same as CCTS elements, except for an opaque signature descendent element that can be stored as a blob or text sequence. Signature generation in the database would not be possible and has to be done after the syntax of the document is marshalled out of the database.

Non-standardized extensions can be modeled as a blob or text sequence.

The extension point scaffolding elements are well-defined and can be modeled the same as the CCTS elements.

Non-standardized extensions that follow the CCTS principles as illustrated in most of the signature extension could be modeled in the database in a like fashion to the rest of UBL.