A database model is a graphical representation of database elements. This is used by database designers to communicate high level design of database structure.  In this article we will learn how to create a database model for a new project.

Starting a new project

When a designer start a new project it can start with program user interface. Most of the time this is done using a program prototype. After the prototype is approved the designer is presenting this prototype to a team for implementation.

Making the requirements

If you are work in a traditional software company probably you will have a team. Depending how the project is organized (scrum, agile or canban) you may be asked to create requirements and presentations for the project.

If the project use large amount of data requirements will include a database and will make recommendations about the database structure but will not go in much details. Sometimes requirements will include a “Conceptual Model” (CM).

Drawing Tools

When yo create a Conceptual Model for a database you need to represent the idea using diagrams, text paragraphs and tables drawn on paper or presentations (PPT) or wiki pages.

Most database designers are using tools to create a database model diagram. These tool are based on IBM UML notation: (Unified Modeling Language). However some companies that are less traditional and more modern are using paper and pencil.


The value of a good conceptual model is very high. One picture can value as 1000 words. However the most advanced the tool the more money your team has to invest to purces these tools and learn how to use them.

Conceptual symbols

The most simple tool you can use is: Microsoft paint. It is not very professional to use Paint but for start I’m going to demonstrate a simple diagram using this tool.

In Paint you can draw simple geometric shapes and lines. Therefore it is a lot of work to create a detailed diagram. For making things easy you will design a simple conceptual diagram showing several entities not the entire database model.

Make small pictures with an encapsulated concept that can include 2, 3 up to 5 entities and several relations. Do not try to explain everything into a single picture.

To represent an entity use a rectangle. To represent relations use a line. If you wish to represent the relation cardinality, use a conceptual notation for the line end. This consist of a bar, a circle or a crow foot like in the picture below:

In the example above I have represented the basic conceptual model notation. Usually the master entity or the parent entity is depicted in top. On the bottom we draw the detail or the child entity.

For one to one relation we can chose to combine all into one entity. Sometimes one to one relation represents an extension or can be used to simulate object inheritance. This is subject to another future article.

This demonstrate how using simple tool we can understad better, faster and intuitive what is difficult to explain in a text. Therefore I encourage you to follow my next articles to learn more about the artistic design of a database.

Database Normalization

One of the most strong principles in relational database design is the principle of normalization. This is the process of organizing data in a database.

There are two goals of the normalization process:

  1. eliminating redundant data
  2. ensuring  comprehensive dependencies

Edgar Codd is the inventor of the relational model (RM). He has introduced the concept of normalization and what we now know as the First normal form (1NF) in 1970. Codd went on to define the Second normal form (2NF) and Third normal form (3NF) in 1971

The normalization process is using several normalization rules:

First normal form (1NF): This is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic indivisible values, and the value of each attribute contains only a single value from that domain.

First normal form enforces these criteria:

  • Eliminate repeating groups in individual tables;
  • Create a separate table for each set of related data;
  • Identify each set of related data with a primary key.

Second normal form (2NF): A table that is in first normal form (1NF) must meet additional criteria for second normal form. Specifically: a table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table.

A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table. Atrbutes that are part of a candidate kye are called prime attributes.

Third normal form (3NF): is a normal form that is used in normalizing a database design to reduce the duplication of data and ensure referential integrity by ensuring that:

1. the entity is in second normal form

2. all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes.

  • 3NF was designed to improve database processing while minimizing storage costs.
  • 3NF data modeling was ideal for online transaction processing (OLTP) applications with heavy order entry type of needs.

Most of applications use Third Normal Form (3NF).

These principles are very scientific and difficult to understand.  However a good designer must know how to apply these principles. This comes within experience, with many try and errors.

Denormalization:

Highly normalized databases are not always the best choice. Sometimes you have to de-normalize a table or two to improve performance.

noSQL

To use a normalized database a program is more complex and require more data search then not normalized databases. Therefore noSQL databases do not use normalization.