Data Modelling with Relational Database

Hello everyone,

In my last blog, we discussed what is a data model and what is data modelling. Now, in this blog, we are going to discuss some of the important topics you should know to perform data modelling with the relational database.

The Relational data model for a database management system is a method of managing data models using SQL.

It was found by Edgar F. Codd.

What are the main advantages of using Relational database management system over NoSQL:

1. It is easy to use because of SQL.
2. RDMS have the ability to perform JOINs.
3. RDMS have the ability to do aggregations and analytics.
4. RDMS have secondary index available.
5. RDMS provides standardization of data model.
6. RDMS provides data integrity.
7. RDMS provides intuitive organization.
8. RDMS provides simplicity.

When one should use RDMS for data modelling?

1. If you have small dataset.
2. If you need ACID transactions.
3. If you need ability to do JOINs.
4. If your business requirements are changing frequently.
5. If you want to do model the data not modelling the query.

When one should not use RDMS for data modelling?

1. If you have large dataset.
2. If you want to store data of different data type formats.
3. If you want high throughputs — high reading speed.
4. If you need a flexible schema.
5. If you need high availability.
6. If you need horizontal scalability.

Now let’s discuss some of the important topics one should know to perform Data Modelling with RDMS.

One should know concepts like OLAP /OLTP, Normalization, Denormalization, Fact and Dimension tables, etc.

We will not go into the deep as this is just an introductory blog, not a tutorial.

So what are OLAP and OLTP?

OLAP(Online Analytical Processing) is used for data analysis for business decisions. It provides supports for complex analytics and for performing ad-hoc queries. They are optimized for faster reads.

OLTP(Online Transactional Processing) is used for less complex queries in a large volume. These queries are mainly — READ, INSERT, UPDATE and DELETE.

If you want to know the total number of packets of chips a supermarket sold, then this will require OLAP. If you want to know the price of the particular chips packet then OLTP will be used.

Normalization:

It is the process of structuring a relational database. It is used to free the database from unwanted insertions, updates and deletion dependencies, to reduce the need of refactoring the database, to make the database more simplistic and informative to the users.

Normalization rules are divided into the five normal forms. But mainly in production, it is normalized only up to third normal form, fourth and fifth are mainly used for academic and research.

First Normal Form(1NF)

All the cells of the database must contain atomic values.

All the values stored in a column should be of the same domain.

All the columns in a table should have unique names.

Second Normal Form(2NF)

It should have reached 1NF.

All the columns in the table must rely on the primary key.

Third Normal Form(3NF)

It should have reached 1NF.

The database should not have transitive dependencies.

Denormalization:

Denormalization is a strategy used on a previously-normalized database to increase performance. In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data. — Wikipedia

It is part of the data modelling process to make data more easily queried.

Fact and Dimension tables:

Fact table — It mainly consists of measurements, metrics, or facts of a business problem. It helps the database user to investigate the business dimensions that help him to call taking to enhance his business.

Dimension Table — It is a structure that categorizes facts and meaning in order to enable users to answer business questions. A dimension table contains attributes of the measures that are taken by the fact table.

Thank You!!

Be Safe!!