A data model provides a visual representation of data elements and shows how they relate to one another. In other words, it demonstrates how your database system is structured. This structure helps you to understand how data is stored, accessed, updated, and queried within the database. It also ensures a consistent structure and high-quality data. Data modeling is used to develop all kinds of databases, particularly entity relational databases.

three levels of database models. There’s the conceptual data model, the logical data model, and the physical data model

We can use MySQL to reverse engineer a model. This means you can create a data model or ER diagram from an existing database. This is essentially the opposite to the forward engineer feature. You can print the model, share it, or apply changes and push it to the database using the forward engineering method

Data warehouse is a centralized data repository that loads, integrates, stores, and processes large amounts of data from multiple sources. Users can then query this data to perform data analysis.

Key characteristics

• Subject oriented
• Integrated
• Non-Volatile
• Time variant

• Choose the business process
• Choose the grain
• Choose dimension
• Choose fact

There are many models that can be used to build a database system. In this video, you’ll explore the following data models. The relational data model, the entity relationship model, and the hierarchical data model. You’ll also review the object oriented model, and the dimensional data model.

The main disadvantage is that it can only be used to record one-to-many relationships between nodes. Each child node can only have one parent node.

M&G can use this model to depict the relationship between their orders and clients entities. Clients are connected to their root node and each order is connected to the related client. While each client can be connected to many orders. M&G can continue to add nodes as required.

Another option for database developers is the object oriented model. This model is based on the object oriented concept. This is where each object is translated to a class that defines the objects characteristics and behavior. A key advantage of this model is that you can define different types of associations between objects, like aggregations, compositions, and inheritance. This makes object oriented databases suitable for complicated projects that require an object oriented approach. This model also relies heavily on the inheritance feature. This is where one class inherits its attributes from another. You can create a parent or superclass, also called a base to hold the common attributes. Each child class that follows inherits the attributes of the parent class.

However, if you do make use of this model, then you need a good understanding of object oriented principles and related programming skills. M&G can make use of an object oriented model to retain attributes between classes. They can create a base or parent class called person entity, that contains attributes and operations. The staff and client classes then inherit these attributes and operations from the person entity class, so each staff member and client are a person.

Finally, there’s the dimensional data model. This model is based on two key concepts, dimensions and facts. Facts are measurements obtained from a process. For example, sales facts obtained from M&G’s business data. Dimensions define the context of these measurements, like a specific sales period. Sales facts measure how many quantities of a particular product M&G sold in each week. The key advantage of this model is that it optimizes the database for faster data retrieval and restructures data for more efficient data analytics.

You’ll explore the dimensional data model in more detail later in this article. You should now be familiar with the different types of data models that can be used to build a database system, and some of their key advantages and disadvantages. You’re making great progress on your database modeling journey.

This reading provides additional examples of how different data models can be used.

Overview

A data model provides a visual representation of data elements and illustrates how they relate to one another. It provides database engineers with an understanding of how data in a database is stored, accessed, updated and queried.  

There are several types of data models that can be used to design a database system. Which model you choose should depend on its suitability in terms of supporting the needs of the business.

The Entity Relationship Model

The entity relationship model is typically represented in an ER diagram that displays a database’s entities and the different relationships between them, where each entity has a set of related attributes.

This model also depicts different types of multiplicities including one-to-one, one-to-many, and many-to-many.

The following ER Diagram illustrates a surgery appointment booking system. It consists of four entities: Doctor, Surgery, Appointments and Patients.

Each entity has a set of attributes, and all entities are connected via foreign keys. According to this diagram:

• A patient can book one or many appointments.
• Multiple appointments could be assigned to each doctor.
• Multiple appointments take place at a specific surgery.

The entity relationship data model supports the surgery’s data requirements, and the ER diagram presents the database structure in an easy-to-use fashion, so you can use it to document your database and communicate its structure with your stakeholders.

You can also build this diagram in an integrated development tool such as MySQL Workbench and implement the database model directly in MySQL.

The hierarchical data model

The hierarchical data model is organized in a tree-like or a parent-child structure, where each record of data has one parent node and can also have several child nodes.

With this model, you can easily find data or add and delete information. However, this model only permits one-to-many relationships between nodes, as each child node can have only one parent node.

The following data model illustrates the database structure of an insurance company. In this diagram the insurance company is structured as follows:

• Department is a root node,
• The Agent and Staff nodes are linked to the parent Department root node.  
• And the Computer and Employment nodes are linked to the parent Staff node.   

You may have noticed that a key advantage of this model is the simplicity of its structure. Also, since each record has only one parent, you can navigate through the database quickly and efficiently. However, you should be aware that, in this model, if you delete a parent node then the corresponding child records are also deleted.

In addition, this model does not support many-to-many relationships, which may be required in some cases (such as in the previous surgery scenario).

It’s also difficult to re-organize the structure of a database to incorporate new requirements. Doing so may disrupt the existing parent-child relationships. You can use ER diagram to represent a more detailed level of this model as shown below.

The Object-Oriented data model

The Object-Oriented data model is based on the Object-Oriented concept, where each object is translated into a class that defines the respective object’s characteristics and behavior.

In this model you can use different types of associations between objects such as aggregations, compositions and inheritance. These features make object-oriented databases suitable for complicated projects. However, making use of this model requires a good understanding of object-oriented programming.

In the following course enrolment diagram, there are six classes:

• Person,
• Student,
• Professor,
• Course,
• Address
• and Enrolment.

Each class consists of a set of attributes and methods.

In this example, each class represents similar objects in the database. This means that similar objects have the same characteristics and behavior. For example, each student registered in the database system has same set of attributes (or instance variables) such as id and type. Also, each object instance of a class utilizes the same operations stated in the class.

In addition, there are different types of associations defined in the diagram as follows:

• Each student and professor are a person (inheritance relationship) and each person is associated with an address.
• Each enrolment involves a student and a course. The enrolment class in this case is called association class.
• Each professor is assigned a course.

Normalization is an important process used in database systems. It involves structuring tables in order to reduce data duplication, avoid data modification implications, and simplify data queries from the database.