Data Modeling

Data modeling is the process of creating a conceptual representation of data and its relationships, which helps us understand how data is stored, accessed, updated, and queried within the database. The output of that process is a formal description of the data that has been modeled, which is called a data model. A data model can be compared to a roadmap, an architect's blueprint, or any formal diagram that facilitates a deeper understanding of what is being designed IBM Data Modeling

A conceptual data model consists of highly abstract levels of data elements called entities and relationships between entities. It presents a high-level overview of the database system through a visual representation of the entities it contains and their relationships to one another. The conceptual model provides the basis for the logical data model.
A logical data model builds on the conceptual model by providing a more detailed overview of the entities in their respective relationships. It identifies the attributes of each entity, defines the primary keys, and specifies the foreign keys. The logical data model serves as an intermediary between the high-level conceptual model and the physical implementation. It describes how the data will be organized in the database and provides a blueprint for building the physical database.
A physical data model is used to create the schema of the database, which is then implemented in the database management system. The physical data model defines the structure of the database, including the data types, constraints, and attributes. It specifies how the data will be stored in the specific database management system being used.

In conclusion, a conceptual data model is a high-level view of the database that is used to communicate with stakeholders about the requirements of the database. The conceptual data model can then be used to create a logical data model, which is a more detailed view of the database that includes the relationships between entities, and the constraints and rules that apply to the data. Finally, the logical data model can be used to create a physical data model, which is the actual database schema that is implemented in a database management system.

Data Warehouse Model (OLTP vs OLAP)

Understanding the difference between OLTP and OLAP models is crucial for effective data warehouse design.

CRITERIA	OLTP DATABASE	DATA WAREHOUSE
Purpose	Execute business process	Evaluate business process
Transaction Type	Insert, update, delete, select	Select
Transaction style	Predefined	Unpredictable
Optimized for	Efficiency and consistency	Query performance/ease of use
Update Frequency	Real Time – business event	Periodic – scheduled – near rt
Update concurrency	High	Low
Historical data access	Recent	Years of history
Selection criteria	Precise, narrow	Fuzzy, broad
Comparison	Infrequent	Frequent
Query complexity	Low	High
Joins	Few	Many
Transaction per day	Millions	Thousands
Data volumes	Tera	Peta
Data	Raw – detailed data	Detailed and aggregated data
Design technique	ER – 3rd Normal Form 3NF	Dimensional/other

From Logical Data Model to Physical Data Model

The transition from a Logical Data Model (LDM) to a Physical Data Model (PDM) is a critical step in database implementation.

Physical Table Structure Should Include:

Technical metadata (job_id, insert/update data, etc)
Technical columns for managing the data quality
Primary and/or secondary indexes of the table to improve performance
Table partitioning functions
Adding surrogate key to simplify join, improve performance, optimize space