RegisterWelcome to a series of How-to’s that explain how to implement the SID for a SID-based database or for the specification of SID-based APIs.
As a technology-neutral information model the “I” component of the SID has never been intended to be implemented as-is. Parts of the SID include multi-level class hierarchies that are not necessarily suitable for a one-to-one SID entity to database table or API implementation.
Using the SID information model as the basis for the design of a database or APIs is not all that much different than data base design for any model that contains class hierarchies or modeling patterns similar to those used in the SID.
These How-to’s should dispel the urban legend about the SID being difficult to implement that was documented in a blog devoted to the legend.
Many, if not all, of the transformations could deferred to database design, if a tool that is used for database design provides functionality to roll up and roll down entities and other transformations described here.
These How-to’s assume that you have a basic knowledge of the SID and its content. They provide step-by-step guidance on various implementation techniques.
Here’s a navigation aid that will guide you through the How-to’s. You can walk through each on this web page, watch a slide show of each, or a combination of both!
Learn About SID Patterns
Patterns are fundamental to the SID’s structure so a knowledge of them is needed to fully understand the How-to’s. They were chosen to provide a standard way to model similar, frequently occurring business requirements. A full explanation of them can be found in the Using the SID guide book.
The following provides a short description of the business requirement satisfied by the pattern.
EntitySpecification/Entity – many entities are described by specifications, which take the form of paper spec sheets or links to specifications that can be found when shopping on the web. The facts (attributes) for a specification, such as weight, dimensions, color, and so forth, are common to all instances of the entity related to the specification.
Composite/Atomic – entities are often grouped together. For example, bundles of product offerings, network elements composed of physical and logical resources.
Entity/Role – an entity can often play many roles, while retaining a basic set of facts about the entity in general. For example, an individual can play the role of customer and employee, but the individual’s name and address don’t change from role to role.
Business Interaction – as an enterprise carries out its mission, it interacts with individuals and organizations in a number of ways. This generalization pattern models the characteristics (entities, attributes, and associations) common to most interactions. As such it allows new interactions, such as customer orders, late payment notices, and so forth, to be easily added to the Information Framework.
CharacteristicSpecification/Characteristic – attempting to model every attribute for new and/or existing entities or attributes associated with new types (specifications) of entities is a near impossible task. This pattern enables new attributes to be dynamically defined, eliminating the need to explicitly model them.
How-to Guides
Learn how to employ techniques for implementing class hierarchies patterns and other patterns.
The techniques regarding patterns that employ class hierarchies described here also apply to any area of the SID where there are several levels in a class hierarchy, such as in the SID Resource domain. It provides guidance on an implementation technique to choose and considerations that must be taken into account based on the chosen technique. These techniques are not unique to the SID, but can be applied to any information model that contains class hierarchies.
These How-to’s describe techniques can be used with a database design tool for implementing the five SID modeling patterns, as well as any part of the SID model that include class hierarchies or entities involved in one to many associations. The techniques can also be used when not using a tool or a partially automated implementation.
Included here are many alternatives that can be considered along with lessons learned that should be taken into account before and after transforming the SID information model to a SID-based data model. The examples start with transforming UML-specified entities into Entity-Relationship specified logical data model entities and then to relational physical data model entities. The Data Definition Language (DDL) generated from the physical data models are provided for some transformations.
The use of these techniques minimizes the impact of adopting new versions of the SID, while supporting improved performance and a more consolidated, simplified view of the SID. The techniques also ensure that the logical perspective of the SID can be exposed via interfaces when employing the SID as part of an integration framework.
In some cases, such as when the SID is to be used as starting point for a physical data base, the logical perspective of the SID is modified to improve performance. In other cases, such as SID-based interfaces, the physical perspective may be generated or developed directly from the SID model as-is or from a subset of the SID model, without employing any of the techniques presented in this section. However, these techniques can also be employed to the SID before interfaces are generated or developed. The considerations presented here will assist in making the decision to expose the SID as-is or after these techniques are employed.
A SID implementer must balance impact of new releases of the SID with practicalities of implementation. Changes made to the SID when transforming the logical perspective to the physical perspective may have to be reconciled manually when adopting new versions of the SID as no comprehensive tools exist today that provide automated reconciliation assistance.
Special Notes
The techniques described here act on the SID information UML model. It is suggested that, if possible, a database design tool that transforms the logical information model (SID UML) to a logical data model (as a first step towards implementation) be employed. A tool such as this should support the specification of transformations described in here that are automatically performed when transforming the logical data model to the physical data model. Many of the considerations described that must be taken into account when transforming the SID UML manually are also not applicable. These will be pointed out at the point where they are discussed in these How-to’s.
The techniques include consolidating entities
How-to’s for Implementing Class Hierarchies
How to Consolidate Entities from the “Top” Down
This technique is used to consolidate a SID class hierarchy by explicitly moving attributes and relationships from abstract super-classes to concrete subclasses. This is referred to as the “top” down consolidation technique. It can also be used to collapse any other class hierarchies in the SID, such the Service or Resource entity hierarchies.
Step 1. Transform the Information Model
The figure below shows a class hierarchy from the Business Interaction ABE before consolidation.
In this example, all attributes from the super-classes above the ProductOrder entity are moved to it as shown in next figure.
This simplifies the implementation view without compromising the structure of the SID, as the super-classes of ProductOrder can be constructed from it if desired for exposure via an interface and to demonstrate conformance to the SID.
Step 2. Develop the Logical Data Model
The next figure depicts a tool-based initial transformation of the BusinessInteraction hierarchy, including CustomerOrder and CustomerOrderItem.
Some of the associations that BusinessInteraction and BusinessInteractionItem have with other SID entities are also shown. Not all are shown because the transformation for these is similar to those shown in the diagram.
For those not familiar with the Entity-Relationship diagramming, in the figure and all similar figures shown in this chapter the “o” means optional, the “ | ” indicates a one multiplicity, the “
” indicates a many multiplicity, and the “ 
” represents a subtype (subclass). If the reader wants more information about Entity Relationship modeling many tutorials/explanations can be found on the Internet.
General Considerations
There are two key implementation issues that are resolved by using this transformation technique. The first is that if entities in a class hierarchy are implemented as-is, then multiple instances of entities are created. For example, in the BusinessInteraction class-hierarchy were implemented as-is to support the ProductOrder entity, then four instances of it must be created, one in each table, BusinessInteraction, Request, CustomerOrder, and ProductOrder. This may be viewed as creating too many tables to represent one concept in the database.
The second issue deals with the resolution of table joins that would be necessary if a class hierarchy is implemented as-is. In the BusinessInteraction example, four tables would have to be joined to retrieve a single instance of a ProductOrder. This may result in unacceptable database performance in high volume applications, such as those required by a Customer Order Management application.
Step 3. Collapse the Hierarchy from the Top Down
The next figure shows the results of a tool-based transformation of the logical data model to the physical model.
The super-classes BusinessInteraction and Request were rolled down (collapsed) into CustomerOrder, and the superclass BusinessInteractionItem was rolled down into CustomerOrderItem. This was specified in the logical data model transformation rules. Notice the inherited BusinessInteraction and BusinessInteractionItem attributes. The validFor (datatype is TimePeriod in the Base Types ABE) attribute is missing in BusinessInteractionItem. This will be discussed in a later in this How-to. Also, an upcoming figure will show some of the inherited associations.
There a number of changes that were made before and after the transformation to the physical data model. Non-needed keys have been removed. For example, requestID was made non-persistent in the logical model and businessInteractionID was removed in the physical model after the proper foreign keys were generated. Some tools allow this to be done in logical data model, so regeneration of physical data model does not require removal of them.
Keep in mind that this is not unique to the SID. However, it may be necessary to retain some inherited primary key attributes in the logical data model to correctly generate the physical data base foreign key attributes. As with any tool that transforms an information model to various stages of data models, practice with the tool is essential! Knowing the desired end result will impact how the transformations are defined and the number of manual changes that are required at each step in the transformation.
Also, it is up to the implementer to decide if the “interaction” prefix on the attributes inherited from BusinessInteraction should be changed to “customerOrder”.
The next figure shows some of the inherited associations.
Notice that the two BusinessInteraction many to many (*-*) associations were transformed into separate entities as part of the transformation. Another set of how-to pages describe an approach to handling these types of associations that should be considered before transforming the information model to the logical data model.
It is up to the SID implementer to decide whether to change the name of the BusinessInteraction-focused names. For example, it may be desirable to change BusinessInteraction prefixes to CustomerOrder and BusinessInteractionItem prefixes to CustomerOrderItem. Notice that the BusinessInteractionRelationship and BusinessInteractionItem_X_BusinessInteractionItem entities now only provide for inter-relating CustomerOrder entities. Support for associations across the subclasses of BusinessInteractions is lost but could be manually introduced by adding associations in the logical or physical data model.
Step 4. Generate the Data Base Definition
Shown next are two extracts from the Data Definition Language (DDL), also call Data Description Language, generated from the physical data model assuming a relational database will be generated. DDL is the starting point for the generation of physical tables in a database. For those not familiar with Data Definition Language, the internet can be used as a starting point to gain an understanding.
CREATE TABLE "Schema"."CUSTOMERORDER" (
"CUSTOMERORDER_ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER ),
"CUSTOMERORDERTYPE" VARCHAR(32672),
"PURCHASEORDERNUMBER" VARCHAR(32672),
"ASSIGNEDPRIORITY" INTEGER,
"ASSIGNEDRESPONSIBILITYDATE" DATE,
"DUEDATE" DATE,
"ID" VARCHAR(32672),
"INTERACTIONDATE" DATE,
"DESCRIPTION" VARCHAR(32672),
"INTERACTIONDATECOMPLETE" DATE,
"INTERACTIONSTATUS" VARCHAR(32672),
"_BUSINESSINTERACTIONTYPEBUSINESSINTERACTIONTYPE_ID" INTEGER NOT NULL,
"_BUSINESSINTERACTIONSPECBUSINESSINTERACTIONSPEC_ID" INTEGER
)
DATA CAPTURE NONE;
CREATE TABLE "Schema"."CUSTOMERORDERITEM" (
"CUSTOMERORDERITEM_ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER ),
"_CUSTOMERORDERCUSTOMERORDER_ID" INTEGER NOT NULL,
"ACTION" VARCHAR(32672)
)
DATA CAPTURE NONE;
Note that the length of the VARCHAR (UML String data types) is the default for the tool used. It can be changed in the physical data model or the logical data model depending on which of these (or both) support incremental updates as part of the transformation to them. For example, some tools don’t provide for incremental changes to the logical data model when transforming from UML; if this is the case, then the changes can be made in the physical data model, with the hope that transformation to it can be incremental!
The second fragment of the generated Data Definition Language follows:
ALTER TABLE "Schema"."CUSTOMERORDER" ADD CONSTRAINT "CUSTOMERORDER_BUSINESSINTERACTIONSPEC_FK" FOREIGN KEY
("_BUSINESSINTERACTIONSPECBUSINESSINTERACTIONSPEC_ID")
REFERENCES "Schema"."BUSINESSINTERACTIONSPEC"
("BUSINESSINTERACTIONSPEC_ID");
ALTER TABLE "Schema"."CUSTOMERORDER" ADD CONSTRAINT "CUSTOMERORDER_BUSINESSINTERACTIONTYPE_FK" FOREIGN KEY
("_BUSINESSINTERACTIONTYPEBUSINESSINTERACTIONTYPE_ID")
REFERENCES "Schema"."BUSINESSINTERACTIONTYPE"
("BUSINESSINTERACTIONTYPE_ID");
ALTER TABLE "Schema"."CUSTOMERORDERITEM" ADD CONSTRAINT "CUSTOMERORDERITEM_CUSTOMERORDER_FK" FOREIGN KEY
("_CUSTOMERORDERCUSTOMERORDER_ID")
REFERENCES "Schema"."CUSTOMERORDER"
("CUSTOMERORDER_ID");