Data Modelling

Data Modelling is the practice of structuring, describing, and organizing information so it reflects real-world entities, relationships, rules, and contexts in a way that supports business operations, analytics, governance, and AI (Artificial Intelligence).

Most of us, are aware of only few Data Modelling techniques, but in reality, there are around 37 of those, shown in below diagram.

Data Modelling Concepts & Techniques

I will try not to go into details in this topic, as we will have separate topics for each of it. First, one must understand the concept of Normalization and Denormalization to get a grasp of data modelling world.

De-normalization: Surely, all of us has worked on Excel. Excel has columns and rows, as do tables in Databases. Consider having employee details in Excel with columns like employee ID, name, mobile number, address, salary, department etc. All this information related to one employee can be stored in one row which is a Denormalization data model.

Normalization: Now imagine, in an employee table, one row is there for each employee who is getting a salary every month. To record the salary in the employee table, there is one salary column in the table. In this case, the first month's salary will go into the salary column then what will happen to next month's salary? There are multiple options to cater to this scenario i.e., 1) replace 1st month's salary with 2nd-month salary. This way, you will lose the previous month's salary. 2) Add another salary column i.e., salary_2ndmonth. This option is not viable as well, as for every month you must add a new column 3) breakdown employee details in multiple tables/ sheets i.e., keep employee details in the table/ sheet: Employee and create a new table/ sheet: Salary for salary on monthly basis. We can bring the employee id to the salary table for reference.

What we did above is called data modelling in the Normalization way. For details, please refer to 3NF data modelling topic.

As mentioned, there are OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing) data models. As we mentioned above, in normalization, there are more than one table, so now when there are more than one table then we need to join all the able using primary and foreign keys (explain in separate topic) using relationship types, explain below.

Relationship Types

• Unary Relationships are where a column is dependent on another column within same table, in other words there will be an internal join.
• Binary Relationships are where there are two tables joined with each other.
• Ternary Relationship are where there are three tables joining with each other with the help of another intermediate table.

Data Modelling Schemes

There are many data. Modelling schemas used in data world, mostly used are given below.

• Relational: Normalization modelling is an example of this scheme where data model is designed to reduce redundancy.
• Dimensional: Star & Snowflake Schema(s) [explained as separate topic] are the examples of this scheme where fact table is joining with dimension tables.
• Object-Oriented (UML): This is a graphical language for modelling software itself. It consists of Class Model which have entity types and relationship types.
• Faced-Based: This is used in Conceptual Modelling where everything is based on Objects and their relationship with each other.
• Time-Based: This is where values of data should have time association with it.
• NoSQL: This is where NoSQL databases are used i.e., graph, document, key-value and column-oriented. NoSQL don’t work like Relational or Dimensional models where one table is joined with another based on Primary and Foreign Keys.