Designing a database in UML

Overview

Traditionally, database design tools are used only within a specific database. In other words,  the design tool included with SQL Server can only be used to design and create a database specifically and solely for SQL Server. Unfortunately, this means the design cannot be used anywhere else in the software stack.

Fortunately, this problem is solved with UML design. UML is extremely flexible as it enables the same design to be re-used within other parts of the software stack.

For example, the same UML design in SilverModel could be used to create a SQL Database, Mongo DB Database, REST API, Web Site etc. Thus, increasing productivity.

This article will firstly focus on exploring the benefits of UML database design. Following this, I will explain exactly how to design a database in UML.

Please note; the UML design referred to in this article is available on GitHub in the Invoicing Microservice Project. Also, the project contains all the database code generated from the UML design. The Invoicing Microservice was designed using SilverModel.

The Benefits of UML database design

There are several benefits of using UML design. These are:

1. The easy design of database structures by using UML(Graphical tools)
2. Clearly, show relationships within the design
3. Generate code from the design (Database, Rest API’s, Web Sites etc.)
4. Excellent documentation tools available

Easy design with clearly show relationships

The first two benefits of UML database design are that the overall structure of the database is easy to create and understand. This visual representation (as seen in the diagram below)  shows the relationships between the classes.

For example, this is the UML diagram for the Invoicing Microservice database.

Unlike code, which only shows a small piece of the database structure, the UML diagrams reveal the complete database outline and the relationships between classes.  This enables an instant understanding of how the database works; compared to the time and effort required to read through and understand the structure within the code itself (and drawing the design on paper to understand it!)

The UML model also enables the design of the database to be updated faster as everything is on the same screen.

Fixing an error in the design phase of an application is a lot cheaper than fixing the same bug in code. As the code fix may require updating lots of files (along with the added unit testing and bug fixing time.) Whereas the UML change will be in one place and very quick to make.

Combining UML Design and Code Generation

When the built-in tools of the database are used to design a database, the design is now locked into using that database, and it is very difficult to use this design elsewhere. When UML is used as the design tool, the design is now independent of the underlying database!

UML Design leads to several benefits

• Any database can be targeted (e.g. SQL or NoSQL)
• The design can be reused for other purposes
• Automatically create the code for the database access(via code generation)
• Reuse the design for other application parts, e.g. Rest API, Mobile App, etc

Target any database

Since the design is independent of the database, any database can now be targeted for code generation. With SilverModel and its data translation layer, it is easy to use the same model to create the database structure for any database, e.g. MS SQL or Mongo DB. UML Design and Code Generation enables quickly swapping the database from, for example, MS SQL to MongoDB, enabling the ability to quickly test which database provides the best performance with the minimum of effort.

Reuse the design for other purposes like serialisation

Instead of just creating the code to access the database, other possibilities exist, for example using the same design for creating code for serialising JSON or XML. This capability adds a lot of flexibility to UML designs since the data is truly independent of where it is stored. Then it is possible to quickly create the code to convert the Database data to JSON. In the past, what would have been a complex and time-consuming programming task can now be done trivially in seconds! Also, it is now possible to easily keep the database code and serialisation layer 100% in sync (with UML design and Code Generation).

Automatically create the code for the database access

From the model of the database, it is now possible to create all the code to access the database. This code can be in any language, and SilverModel comes supplied with code generators that support C# and Entity framework(ADO.Net coming soon)

Why spend days or weeks writing the database structure code when SilverModel can create this in seconds!

Reuse the design for other application parts

With UML and SilverModel, it is possible to reuse the database diagram the create other parts of the application software stack. e.g. Rest API, Mobile App etc.

Check out the Invoicing Microservice on GitHub for an example of this. Also, this blog will have articles on how to do this shortly as well.

How to design a database in UML

Now let’s dive into how to use UML to design a database. The Invoicing Microservice will be used as an example in this section.

Here is a table that shows some examples of how UML maps to an MS SQL Server database.

Use the above table to quickly get started with using UML to design databases, as it shows now UML elements can easily be mapped to the database parts.

As shown above, with a UML Class Diagram, it is easy to identify the different parts of the database, e.g. the tables and columns and the relationships between the tables.

Creating a database in UML.

The starting point of any database in UML is a class diagram. This diagram is what will hold all the elements of the database.

At this stage the most important things to do are

1. Name the Class Diagram.
2. Add any Code Generator or Stereotypes that are required.

The Code Generators or Stereotypes contain the extra metadata required to design the database.

For the Invoicing Microservice example, the following code generators where added

• Datatypes (basic datatypes for designing, e.g. Integer)
• Database General Metadata (e.g. The metadata for a primary key)

Initially, the class diagram will be blank, so now database elements need to be added to it.

Adding Database Tables to the UML Class Diagram.

A UML Class represents database tables in UML.  Add a new UML Class to the diagram for each database table that needs to be created. Then give the Class the name database table will be assigned.

It’s the flexibility of the UML Class when designing databases that things start to get interesting!!! Here are a few ideas of what else a UML Class Diagram can be used for when designing a database.

• Metadata (to store any other database table property)
• Reuse the UML Classes for database transactions/views (upcoming article)
• Have some as database tables and others as serialised data (e.g. objects the will be saved as JSON in the database)
• Design a MongoDB’s database and documents
• Create Key/Value databases.

The above example demonstrates how designing a database in UML opens up a lot of possibilities that will help to improve

When designing a database, keep in mind how else the data is going to be used, and how to capture that information in the design. In UML, a class could be just a database table, but also it can be so much more, e.g. it could define a REST API, Web Screen etc., this is the power of UML.

Also, it is possible to create Code Generators for all of the above in SilverModel, which will then enables massive gains in productively.

Adding Database Columns to the UML Class Diagram.

UML Attributes represent database Columns in UML. To add database columns to a table, add a UML Attribute to the relevant UML Class.

Here is an example of database columns (UML Attributes) added to the Address table (UML Class).

After the UML attributes have been created, it’s time to set the following metadata for them (using EF Core as an example).          

• Set the “Name” for the database column.
• Set the “Type” for the database type, e.g. Int.
• Set the “Primary Key” metadata to “True” if this attribute is part of the primary key.

Here is an example the metadata that was set for the AddressId, where the Name, Type and Primary Keys values are set

The above are the basics of what can set for a database column. If extra details are required about the columns (e.g. Length, Numeric Precision), this can be done by extending the metadata for the “Database General Metadata” code generator.

This is an example of the Metadata(stereotypes) that are defined in the “Database General Metadata.”

The above example shows three Stereotypes (Metadata) that will be applied to the Attribute. The above can easily be extended to capture any data that is required by adding another Stereotype.

Adding Database Indexes to the UML Class Diagram

Database Indexes can be represented by adding some metadata to a UML Attribute. Then set the index metadata to the name of the index to be created.

Example: Here is InvoiceNo attribute has the index metadata set. The metadata can then also be used during code generation to create the required indexes automatically.

Adding Database Foreign Keys in UML

One of the more powerful features in a relational database is Database Referential Integrity. The way to design these relationships in UML is to create an Association between two of the UML classes.

This association can be used to assign the following for the relationship

• The direction of the relationship
• A name for the relationship
• The cardinality, e.g. 1 to many

It this example we can see that an association been used to define a relationship between the Customer and Address. The Customer can have many Addresses.

Summary

I am hoping that this article has opened your eyes to the power of designing your applications using UML. UML can be a game-changer when designing a Database and then using the power of code generation to create your database code.

UML Database design is only the start of the journey with UML; this same model can then be used to create for example

• REST API’s
• Mobile Screens
• Web Sites
• etc

See the Invoicing Microservice for an example where UML design and code generation has been implemented. Also, the database design is reused in other parts of the design to provide more advanced functionality.

If you have any questions, please contact me, and I will be happy to help.