As we know that it’s always easy to store complex data types in SQL database servers. This is possible with the data modelling method in Sql server. The data modelling is a data abstraction method that helps to store the complex and large amount of data in Sql database server. Today in this article, I am going to explain important concepts of data modelling in the SQL server. So what you are waiting for, let’s get started our journey with Data modeling in Sql Server database.
The Core architecture of the data model explains the environment and nature of the work it performs. Let’s discuss the core Architecture of data modelling
The building blocks of core data model architecture included are,
Core: Core is considered to be one of the essential components of data model architecture. The main purposes of these Core components are, developing the data object models, validating, and maintaining the data objects. And also helps to share the valid across architecture (main functionalities included are interoperability, investment optimization).
Data Model: This data model component defines the objects of a domain, the inter-relationships, and the properties of the database design. There are three types of data models available such as conceptual, logical, and physical. Conceptual data models are the highest level, logical data models are a more formal and unique type of data representation, and whereas physical data models are the most detailed and sufficient data generation model.
Architecture data: The Architecture data are nothing but products and related analytical tools which are stored in the form of object classes, feature, properties, data attributes, and inter-related database objects.
As I said earlier, the data models are used to store the complex data objects in the database server, so now I am going to explain a few advantages of the data model;
There are mainly three types of data models available such as,
A business data model is a data organized view of SQL database server concepts and helps to build their relationships. The main purpose of creating a business conceptual data model is to the establishment of data entities, attributes, and relationships. In this data modeling conceptual level, the main drawback is that there is hardly any important details are available on the database structure. This type of Conceptual data model is created by business stakeholders and data architects.
In this example, customer and product are two entities available. Here the customer number and customer names are any two attributes. The product names and its price are the attributes of the given product entity. Here is the sale establishes the relationship between the customer and product.
1. This conceptual data modeling offers organization-level coverage of the business concepts.
2. This type of conceptual data model is mainly designed and developed for a business audience.
3. The business conceptual data model is developed independently of many hardware specifications such as data storage capacity, data location, or any software specifications like database management storage (DBMS) vendor and technologies. The main purpose to use this model is to represent the user data as a “real world” data.
4. The conceptual data models are also known as Domain data models mainly used to create a common vocabulary attribute for all the business stakeholders only by building basic data model concepts and scope of the model.
2. Logical data model:
The logical data model is mainly used to define the data structure elements and also establishes the relationship between them. One important thing is that this logical data model further adds information to the business conceptual data model elements. The main purpose of using this logical data model is to offer a foundation for the physical model. The following structure explains the logical data model,
At this logical data model, there are no primary and secondary keys are defined. During the time logical data model, the user needs to perform verification and adjust the connector details for relationships.
The following are the few characteristics of the logical data model,
1. This logical data model describes the requirement for any single project but this can also integrate with other logical data models depend on the scope of the project.
2. The logical data model is designed and developed independently from the database management system (DBMS).
3. The logical data attributes will hold specific data types with necessary precisions and length.
4. Supports normalization processes to the data model applicable to 3NF.
3. Physical data model:
The physical data model describes the database-specific implementation of any data model. This offers a database abstraction level by a physical data model. This type of data model also helps in visualizing database structure which replicates many sections such as column keys, primary keys, constraints, indexes, trigger levels, and any other relational database management systems features.
The following structure explains the nature of work;
1. The physical data model always supports defining the data need for any single project or data application, and it maybe integrate with any other new physical data models depends on the project scope.
2. This physical data model consists of the relationships between tables which addresses the cardinality and nullability.
3. The physical data model is developed for specific versions of database management systems, data locations, data storage, or any advanced technology to be used in the project.
4. Defines the columns which hold exact data types, lengths assigned, and any default values.
5. Primary and foreign keys, view types, access profiles, indexes, and data authorizations must be defined in the data model.
Let’s explain the types of facts that can be used in data modelling in SQL server,
1. Full addictive fact in data modelling:
Example: Revenue of the business
2. Semi addictive facts:
3. Non-addictive facts in data modelling:
Data margin –rate is non- addictive data fact
Data margin – rate = the margin data will be available in the form of the amount of revenue
4. Fact-less data table:
This is nothing but a fact with no data measurements
5. Surrogate keys:
These surrogate keys are just a replacement to the primary key, and they are system generated sequence numbers.
1. To replace the data table with the primary key
2. To generate unique data values
3. Surrogate keys are used for both dimensions table and fact data tables.
Now it’s time to know the difference between star schema and snowflake schema;
|Star schema||Snowflake schema|
|In this case, all the data dimensions are directly connected to fact table dimensions where there are no hierarchies||this is just a replica of star schema but the data dimensions contain sub-dimensions so the user will get dimensional hierarchies|
|Consists of Denormalized data set||Consists of the normalized data set|
|Less joins available and query performance is fast||More joins available and query performance is slow|
|Business people and stakeholders can easily understand the data hierarchy||Business people and stakeholders cannot able to understand the data hierarchy easily|
|This star schema hierarchy are designed on the base of DWH OLAP applications||This hierarchy is designed on the base of OLTP and no OLAP application used|
|Contains fewer data tables and they are consolidated||Contains more tables and they are elaborated|
|Too many relationships available between fact and dimensions||Fewer relationships available between fact and dimensions|
Let me explain the advantages of data modelling in Sql server:
To develop a data model one should know physical data stored characteristics.
In this article, I have tried to explain the important concepts of data modeling in Sql server. This data model is used to create a model where we can store complex data types. The data model also ensures the consistency in naming conventions; maintains default values, semantic data structure, and also for securing the existing data types. I hope this article may help a few of you to learn and enhance the knowledge of data modelling in Sql servers. The beginners can also interact with experts through social media forums.
25th October | 12:00 am