Hello Readers, This blog is related to the data modeling basics. We will be looking at some interesting data modeling techniques and how to build relationship between different tables. This blog will help while building data model and ETL processes.
We will be discussing following topics:
Important terms in Data model
What is modeling in Data ?
What is Star Schema?
Why Data Modeling is important?
Lets start by discussing what are important terms:
1.Important terms
1.1. Primary key: This is the column name that appears only once in the table row. The values in this column cannot be repeated because these are unique in its own context.
Examples: Roll number of students, Employee name in companies, Product ID or Product name, Product type, Type of furniture. These all attributes will be only appear once in the row and will be unique. Now lets see what is the meaning of foreign key.
1.2. Foreign key: Foreign key is defined as attribute which is in the table but linked to the primary key in other table. If we have products table and orders table. In products table we will have unique products and orders table can have orders received for the whole month. Suppose relationship exists between the PRODUCT_ID (Primary key) column in products table and PRODUCT_ID (Foreign key) column in orders table, then the PRODUCT_ID in orders table is foreign key. This is because orders table can have MULTIPLE product_id's. Same product is ordered by multiple times, thus we can have multiple rows with same product_id in orders table.
1.3. Fact table: Fact table is main table which has primary key in it. This table is used in STAR schema technique. Fact table is has primary key and fact table is related to number of dimension tables. STAR technique is the technique which has fact table in the middle and there can be multiple dimension tables around it. Please see the corresponding diagram of star technique.
1.4. Dimension table: In the above STAR technique we can see that DimReseller, DimDate, DimEmployee, and all other starting with Dim are dimension table. These tables are linked with the fact table with foreign key. The foreign appears in fact table as primary key as well. In the STAR schema there are many dimension table and only one fact table.
1.5. One to One Relationship: This relationship means that every record in one table is associated with exactly one record in other table. Examples of one to one relationship can be: Capital city - Country, Employee - Manager, Email - User account
1.6. One to Many Relationship: In this type of relationship we have record from one table related to many records in the other entity or table. This is most common type that we use in data modeling field. In this relationship, there can be more foreign keys in dimension table but only one primary key in fact table. One to Many relationship is used in STAR schema most of the times.
2.What is modeling in data?
Data modeling is the technique to note information in simple terms to have better communication between the teams. This is one of the most used and most applied concepts that we have to perform before doing analysis. In data modeling we define the relationship using primary and foreign key.
We can think of data modeling as the complex model of information. For example maps are the complex form of landscape drawn on the paper to understand in simple form. Blueprints are the simple drawing of the architectural models. Similarly, data model is simple but understandable form of the information.
3. What is STAR schema?
What is STAR schema in data modeling? This is the most important and useful techniques when it comes to getting data and tables ready for the analysis. There are lots of modeling tools which can be used, such as Power BI, Excel, Power Query. But I use most of the times Power BI to make the relationships and connecting tables. Advantages of the techniques is that we have overview of the connection in one overview when we look at the data model. Disadvantage of this schema is that it can be only used in structured data. Lets see in detail what does STAR schema is:
STAR schema is data modeling technique which enables to have the map of information or data in very effective manner. Suppose, we want to create a relationship between different tables, we use this schema. The main advantage of this schema is to have one entity in the center and 'N' number of entities in the background. These entities surrounding the center table are called dimension table and center table is called fact table. Fact table are the main tables having the primary keys.
It is important to have the primary key in the fact table. Primary key cannot be deleted from the fact table, because it has some relationship with foreign key in dimension table. I have already explained the STAR diagram but lets see how the fact table and dimension table looks in the diagram form.
Figure: STAR Schema Diagram
https://medium.com/@nimanthaF/data-modelling-techniques-star-schema-f1077a1cced7
4. Why Data Modeling is important?
These models are important in terms of communication between the teams. In any company or project there can be different types of professionals working such as data administrators, data analyst, business analyst , managers, developers, deployment team and many more. There has to be understanding of each team in order to communicate the data terms and project outcomes in simple form. In this case, data models make it easy to communicate the vital information in simple form. There are tools available in todays era which allows to build data models without programming.
To wrap up we look into the terms related to data modeling, what is data modeling, STAR schema and importance of data modeling. In simple terms data model is similar to map of information which tells user about the data relationship with each other.
Thank you for reading,
Soham Sanjay Shinde