Data modeling and database design in ABAP, part two

Learn modeling techniques for database design and data modeling in ABAP with this multi-part series. Part two introduces the SAP structured entity relationship model (SERM).

In the last installment of this weblog series, I gave an introduction the the SAP Data Modeler. Now I will explain the meanings of the diagrams and 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 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 installment. 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').

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 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:

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 installment 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 adopted 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. 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 installment, I want to discuss further relation types:

  • referential;
  • referential-conditional, and
  • external

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

Data modeling and database design in ABAP, part three


This content is reposted from the SAP Developer Network.
Copyright 2006, SAP Developer Network

SAP Developer Network (SDN) is an active online community where ABAP, Java, .NET, and other cutting-edge technologies converge to form a resource and collaboration channel for SAP developers, consultants, integrators, and business analysts. SDN hosts a technical library, expert blogs, exclusive downloads and code samples, an extensive eLearning catalog, and active, moderated discussion forums. SDN membership is free.

Want to read more from this author? Click here to read Tobias Trapp's weblog. Click here to read more about ABAP on SDN.



This was first published in December 2006

Dig deeper on SAP ABAP

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchManufacturingERP

SearchOracle

SearchDataManagement

SearchAWS

SearchBusinessAnalytics

SearchCRM

SearchContentManagement

SearchFinancialApplications

Close