5.4 Modeling Techniques

Defining a model of your data is the first step when building a new application. If the structure of your database is not flexible enough and if it cannot be extended easily, your entire application will suffer. After an application has been built, it is hard to change the structure of the database it is based on. Therefore it is worth the effort to think about the database model extensively. In the past few decades, a lot of people have been thinking about the right ways to build database models. In this section you will find out about the basics of building database models. A topic like modeling data can never be covered sufficiently, but we will try to guide you through the most important models and to show you what can be done to make your databases more flexible and easier to extend.

5.4.1 Entity Relationship Models

One of the most intuitive models for building databases is the Entity Relationship Model (ERM). Today ERM is the state of the art when designing large database projects. The idea behind ERM models is to display the interactions between various entities.

Entities are objects for storing various attributes that are relevant for the system you want to build. Depending on the scope the system has to cover, a certain entity will or will not be part of your model.

An important decision that has to be made is how many details and exceptions will be covered in your model. The more details your system will cover, the more complex it will be.

Entities that cannot be used to store attributes are not entities and have to be omitted because they cannot be transformed into relations. Entities are always objects and are labeled with nouns.

Attributes are always stored in combination with entities. An attribute describing an entity can either be part of a second entity or not. This shows that entities are related to each other.

The way ERMs are displayed is unitary. Almost every book uses a different way to draw ERMs and so it is not possible to include a set of common rules how to draw an ERM best. Some things, however, have all ERM drawings in common: Entities are displayed as boxes containing the name of the entity. Entities related with each other are connected using lines or arrows.

Figure 5.1 shows a simple ER diagram.

Figure 5.1. A simple ERM model.

The model consists of two entities. The entity Student has two attributes called first name and surname. The second relation is called Training and has two attributes as well. Every training course has a unique course id and can be done in a certain semester. Of course, students and training courses have a lot of additional attributes, but for an easy model, it is enough to work with two attributes for each entity.

Student and Training Courses are in a 1 : n relation, which means that every student can attend more than just one training course.

Keep in mind that Figure 5.1 shows one way of drawing an Entity Relationship model. Depending on which book you are reading, ERMs might look slightly different.

5.4.2 Transforming ERMs to SQL

The target of ERMs is to have a graphical way of displaying the way various objects in your database can interact with each other. However, what databases really need is plain SQL, not bitmaps. Therefore, ERMs have to be transformed to SQL code that can be used to build up a data structure inside the database. For this operation some rules have been defined, which will be discussed in this section.

5.4.2.1 Rules for Transforming ERMs to SQL

Let's see what has to be done to make SQL out of an ERM:

• Entities to tables Every entity in your model will be a table in your database.

• Attributes to columns Every attribute of an entity will be a column of the table the attribute belongs to.

• Identifiers to primary keys Every unique identifier of an object will be a primary key. Every entity needs exactly one identifier.

• Relations to foreign keys Entities that are related to each other have to be connected using a foreign key. This way the integrity of your data will be guaranteed.

With the help of these simple rules, any ERM can be transformed to SQL.

5.4.2.2 PostgreSQL Specifics

PostgreSQL provides some important add-ons to traditional relational concepts. The problem is that these things can hardly be used when working with ERMs. Just think of things like inheritance or arrays using the original ERM will not allow you to use these features of PostgreSQL.

5.4.2.3 Automatic Transformations

Today some tools for building data models are available. Some of these tools are able to generate platform-specific SQL commands out of ERMs. This way it is an easy task to build complex models. The problem with tools like that is that at the moment this book was written, no such tool was available for PostgreSQL. Things will change in the near future and PostgreSQL will be supported as are many other databases like DB2 or Oracle.

5.4.3 UML

UML is short for Unified Modeling Language. The idea behind UML is to have a tool for creating software more effectively and more reliably. UML is a language for describing the interaction of objects and is very similar to Entity Relationship Models. Because Entity Relationship Models can be drawn in many ways and because there is no unified standard, UML is very often used as the common language everybody can understand easily. Because UML is a language for describing things, it can be used to build models for complex subjects and to visualize the way various components interact with each other. Models built with the help of UML can easily be understood because every UML model is a graphical description of what has to go on inside an application.

UML was developed by Rational Software and its partners. It is the successor to the languages used for modeling found in the Booch, OOSE/Jacobson, OMT, and other methods.

Today UML is widely used as the standard modeling language and helps to reduce the time needed for developing a software product.