Data Modeling and Database Design in ABAP – Part 2

In the Data Modeling and Database Design in ABAP – Part 1 of this weblog series I gave an introduction the the SAP Data Modeler. Now I have to explain the meanings of the diagrams and especially discuss foundations of the structured entity relationship model.

SERM

The structured entity relationship model (SERM) is an extension of the entity relationship model. If you look at SERM-diagrams and study further examples you will recognize following differences between SERM and ERM (entity relationship models):

• Besides the usual entity-types and relationship-types we have entity-relationship types that are used to express existence dependencies using directed arcs,
• n:m relations of ERM are expressed in SERM like in an relational model by two 1:n relations. Here there is a difference to the classical ERM-model: in ERM we use link relations as representation.

SERM uses additional graphical elements with origin in relational database design. Some people find it confusing because usually the semantic model (written down in UML or ERM) is clearly separated from the relational model. Compared to ERM we have a bigger expressiveness and usually SER models are more related to the physical data model like views or transparent tables in the data dictionary. That’s why I think of SERM as a very useful for data modeling as well as well as database design.

Let’s have a look how it works. In ER-diagrams we have a cardinality between entities.

Let’s consider an extension of ERM that allows to define min/max cardinalities for each relation type from the viewpoint of the corresponding entity types. Let’s consider a relation “visits” in the picture below:

• Zero or more (0,*) students can visit a course and
• a course is visited by at least one student (1,*).

In SERM we define min/max cardinalities for each relation type from the viewpoint of the corresponding entity types. I will describe how this data model is expressed in SERM later on.

Existence Dependency

Let’s have a look at an important special case – in fact it is a simplified from the example of the last blog instalment. We have following min/max cardinalities:

• The business hours (of a business partner) consist of (0,*) intervals.
• Each interval must be related to exactly one business hour.

In the diagram below the arrow expresses existence dependency (in fact all arcs in SERM directed). The double arrow means that multiple business entities can take part this relationship, the small vertical line means that it is allowed that it is possible that there is no interval associated to a business hour:

We say that the intervals are existence depended on the business hours.

In the ABAP DDIC we can model this in two ways:

• We build a composite key: The primary of business hours is extended by another data element.
• We define a foreign key linking the interval to the business hour.

If we chose the first way we enforce existence dependency in the relational model: we can’t define an interval without the primary key of the corresponding business hour. If we define a foreign key we have to take care that we can’t enforce the referential integrity in ABAP – we can’t even define a constraint on the database layer that the foreign key value must not be NULL.

If we look at SAP data models we will recognize that this existence dependency plays a major role: Nearly every business object is composed and consists of a header (think of BKPF) together with components (often called ‘positions’).

In SAP SERM SAP stresses the existence dependency by calling this relation type “hierarchical” and adds the letter “H” to the arc as you can see in the last instalment of the weblog series.

Compared with ER models SER models are directed and they are written down from left to right: The entities on the right are dependent from the entities on the left. On the left side there are independent entities and they are linked to depended entities.

In the rest of this blog entry I want to describe some SAP specific extensions of SERM: SAP SERM.

SAP SERM

SAP used SERM for semantic modeling and defined their own notation. In fact I used the SAP notation (and not the notation of the inventor Elmar Sinz) for existence dependency so far.

It seems to me that SAP SERM useful for data modeling in ABAP. One reason is in ABAP we don’t care about the physical database table: the ABAP DDIC represents a table independently from the underlying database management system (MySQL MaxDB, MS SQL Server, Oracle, DB2 and so on). As a consequence in ABAP we don’t need to spend that much effort in describing technical details. Most non-SAP applications (think of programs written in Oracle PL/SQL) are developed and optimized for a single database management system and even an change of its release can have the consequence that we change the program and of course its documentation.

In ABAP it would be useful to do semantic data modeling and link this semantic data model to the ABAP DDIC. This approach seems appropriate to a programming environment that is independent from underlying database management system.

Dealing with n:m Relations: Aggregational Relationships

As I mentioned above SERM doesn’t represent n:m relations. I think this is a consequence because SERM uses concepts from relational models and therefore uses the technique for relational models to split n:m relations into two 1:n relations.

In the example above we introduced a new entity type I called “visits”: a student visits a course. This relation “visits” is an aggregation of two start entities (student and course) and there is an existence dependency of each two to the entity type “visits”. SAP calls this aggregational relationsship. Here we have a difference between the ERM and SERM: in ERM we model we represent this relation type be a new relation called link relation. This relation will be represented by a new table (called link table) and contains two attributes for student-ID and course-ID together with foreign keys. From the view of SERM this would be more appropriate if there is a special entity type – perhaps a confirmation for participation of a course: we represent it as a new entity type that has its own attributes. From the view of an “aggregation” it would be more natural to define a composite primary key consisting of both IDs and represent the relationship this way. So here is the corresponding SERM diagram:

Generalization

From object orientation we know the concept derivation. In the example from the last instalment of this blog series there was an example of this concept: A business hour can be a visting hour, a calling hour or a goods receiving hour:

When we derive our (semantic) data model from an object model the generalization comes trivially from derivation and we have to solve the problem whether we create a single table for each class or different tables.

Further Types of Relationships

SAP defined some more types of relationships, some of them I will discuss in the following instalments of this blog series:

• referential and
• referential-conditional.

In my opinion all relation types are well described in the SAP library, just look at the chapter about the data modeler (BC-DWB-TOO). There you will find explanation for time/customizing dependency, too.

Hierarchy of Data Models and Business Objects

Usually Entity Relationship Models are huge and impressive monsters. Database designers are proud to put them onto walls so that every manager can see that they are working on really complicated stuff. I have to admit that this makes fun – but does this justify nearly incomprehensible diagrams?

In my opinion we should use the concept of existence dependency to create hierarchical diagrams. In an “bottom up” way we can define data models for independent entities and all (existence) depended objects. Later we can “glue” all data model by creating a new data model that contains only the depended objects and defines existence dependencies between them.

This way of modeling corresponds to object orientation in a natural way; we can think of independed entities as objects having associated objects. In fact SAP did this when defining Business Object s- data modeler allows us to define data models for them.. SAP very early adopted to the concept of object orientation to represent entities and even whole business processes. In the view of the SAP Data Modeler a Business Object represents a data model – a set of entity types with a common interface. Usually a Business Object is composed and therefore consists of composed or even hierarchical data models. You find good examples and I want to encourage you to study the Business Objects ‘Outline purchase agreement’, ‘Purchase scheduling agreement’ and ‘Purchase contract’ and the related data models.

If there are links between entities of different data models we call the relation type “external”.

In my opinion using those techniques you can define understandable data models. And If you need to impress your boss you can still link them together: just look at the data model CRM.

Summary

In this blog entry you learned about SERM, SAP SERM. At the end I gave a brief glimpse at the relation of SAP Business Objects and the relation with data models. In fact I think that especially this aspect is very advanced it could be worth for theoretical as well as for practical purposes to use this methodology outside ABAP development. Most books and papers that describe object oriented analysis and development mention always the same best practices to bridge the object-relational gap but don’t discuss hierarchies of data models.

In the next instalment I want to discuss further relation types:

• referential,
• referential-conditional and
• external.

Therefore I will have to introduce the concept of strong and weak existence depedency.