Physical Data Model vs. Logical Data Model

What Is a Data Model?

A data model is a visual representation of data elements and the relationships between them based on real-world objects. Data models reveal and define how data is connected within business processes and support the creation of efficient information systems or applications. For example, in business intelligence, a data model defines what kind of data users can utilize within their analytics.

To get a better image of what a data model looks like, check out the examples in our previous blog post, which covers the most used data model types. This will help you to create your own data model, but to achieve a fully functional and change-adaptable version, be sure to keep reading.

What Is a Logical Data Model?

A logical data model is a data model that provides a detailed, structured description of data elements and the connections between them. It includes all entities — a specific object transferred from the real world (relevant to business) — and the relationships among them. These entities have defined their attributes as their characteristics.

Logical data models bring together the two most vital basics of application development — business requirements and quality data structure — into a visual representation. Business analysts and data architects are responsible for creating these models. They map relevant business processes and reveal the business requirements in order to create a model which meets company goals. Moreover, they prepare a technical map of rules and structures depending on the scope of the project.

Logical Data Model Examples

The following schemas represent logical data model examples for different purposes. A logical data model diagram/schema contains all of the entities, attributes, and relationships in a visual form. Names are given to entities and attributes based on how they are actually used in a business environment.

The first logical data model example is related to a small e-commerce business. This logical data model captures orders of various products via an online store. All of the data detailing each customer's order is stored within the Order Line. One order includes information about the product and customer from the individual entities; Product and Customer. Within these tables, there is no duplicate information — if a specific customer has ordered something in the past, their data is already stored in the Customer table and with a new order this personal data is reused without the need to save it again. The same applies to the Product table — information about a specific product is stored only once, but this product can be a part of different orders.

Then, to get detailed information, the order date is added to each order.

Another, more complex, logical data model example refers to a model for purchasing bank services. This logical data model diagram captures information about customer accounts. The information will be stored in Account, where data about a customer's personal details as well as additional information about the account will be gained from the individual entities; Customer and Account Type. Moreover, this model is also ready to store information about different bank purchases made via the customer's account, within the Purchase entity. Bank service details are stored in Bank Service and Service Type.

Once the logical data model is finished and approved, the physical data model can be tackled.

What Is a Physical Data Model?

A physical data model specifies how the data model will be built in the database. It outlines all table structures, including column name, data types, column constraints, primary key and foreign key with indexes to the relevant table column, relationships between tables, stored procedures, and views.

The responsibility regarding physical data model creation usually lies with database administrators and developers. Information systems and software applications heavily rely on interactions with physical databases. Physical data models need to be designed and implemented correctly. It is challenging to modify physical data models once data from the existing application has been inserted into databases.

Physical Data Model Examples

The following physical data model examples are derived from the logical data model examples. It is worth noting that while logical data models serve as blueprints for physical data models, their attributes and names do not have to be the same as physical data model tables and columns. In short, objects in physical data models can be named differently, but they still have the same purpose.

Each logical data model example is translated into a physical data model schema. The first, which refers to an e-commerce use case, can be transformed into the following:

Entities have been transformed into tables and attributes into table columns. Their names are also translated into technical terms — how they could be implemented and stored in the database. In addition, each column's data type has been specified.

For bank service purchases the schema might look like the following:

Each of the physical data models noted above is simplified for illustrative purposes; in a real-world environment, these physical models would be significantly more detailed and offer a broader perspective of the specific areas in question. You can try creating a logical and physical data model within our product.

Physical Data Model vs. Logical Data Model

Physical and logical data models both form different stages of the data model creation process and both are created with different purposes. Let's see how they differ.

What Is the Relationship Between Logical and Physical Models?

In order to place the logical and the physical model into an overall context, it is necessary to highlight the connection between them during the data model creation process.

A logical data model is created to cover all of the important enterprise processes and requirements of the information system or software application in question. It is at this stage that organizations are able to understand how the data flows within their business.

In contrast, the physical data modeling step involves the clarification of technical and performance requirements. As such, companies need to ascertain what kind of systems they will build prior to this: There are two strategies that impact performance:

• Normalization refers to a formal approach that includes a set of rules that guarantees attributes are placed in the correct tables. The setup of a normalized database involves removing redundancy so only a single copy exists of each piece of information to achieve data integrity.
• Denormalization is used in systems, where the emphasis is placed on speeding up search and analysis by adding data duplicates or grouping data. It is usually applied above normalized databases to make data retrieval faster.

Once performance requirements are established, a logical data model is extended by converting entities to tables and specifying data types and relationships within the chosen database. The end result is a physical data model that is ready to store business data and generate a multitude of queries and reports.

Logical Data Model Benefits

The key benefits of a logical data model include:

• Helping companies to identify and improve their business processes.
• Enabling companies to better describe data elements by choosing the attributes which are necessary for their data models.
• Serving as a pre-implementation phase that minimizes the risk of implementing data models and aligns requirements post-launch.
• Supporting data reuse and data sharing.
• Providing companies with detailed blueprints which can be customized and adapted to specific technologies.

Physical Data Model Benefits

The key benefits of a physical data model include:

• Providing companies with a visual representation of a database structure.
• Helping to easily translate data models into a database schema.
• Affording businesses better preparation and, as such, helping them avoid the frequently high expenses associated with error correction.

How To Create Data Models With GoodData

GoodData provides users with a bridge between their data sources and the reports they want to create. You can connect your own data source to GoodData's API-first analytics platform, generate both a physical and logical data model, and analyze your data by creating metrics, insight, and dashboards.

The logical data model forms an essential part of GoodData's semantic layer, helping to transform complex data into reusable abstractions and context-aware metrics. Note that in GoodData, the logical data model is often referred to as the semantic data model.

After connecting a database to the GoodData analytics platform, your data model is automatically detected. The GoodData platform has the built-in capability of enabling the auto-generation of a logical data model once a physical data model is loaded. Users do not have to spend time creating a logical data model and instead they can generate them by selecting all the required tables from the left panel and adding them with one click. As a result, they have more time to focus on the analytics — creating metrics and dashboards, embedding analytics into business applications, etc.

On the other hand, you can manually build a logical data model in three ways, using:

• Drag-and-drop in the LDM modeler
• GoodData API
• Python SDK

In the following example, we can demonstrate building a logical data model in the LDM modeler. You can drag and drop empty datasets into your canvas, name them, add attributes to each dataset, connect datasets by manually moving an attribute from one dataset to another dataset, and set the connection between them based on their primary and foreign key.

Once your logical data model is created manually, you can generate a Physical Data Model In the LDM Modeler — each field of your physical must be mapped to a column in your source table (represented by a table in your data warehouse).

The benefits of data models in GoodData include:

• The ability to apply custom fields, additional datasets, and different business names per user/tenant when creating/modifying a logical data model for a given workspace.
• The flexibility for users to create physical and logical data models automatically or manually.
• The inclusion of a semantic layer; responsible for translating source data into business terms understandable by non-technical users.
• The ability to reuse the same logical data model and only swap the data source identification.
• The ability to create a logical data model separately and then use data mapping to connect the physical data model with your pre-defined logical data model.
• The layer of abstraction provided by the LDM; removing the need for users to interact with the physical data model.
• The creation of context-aware and reusable metrics which can be used within insights and dashboards.

Want To Try It Out?

Sign up for the free GoodData trial and try connecting your database to the GoodData platform, build physical and logical data models, and visualize your data. Alternatively, request a demo and talk with one of our experts today.

If you are interested in GoodData.CN, please contact us. Alternatively, sign up for a trial version of GoodData Cloud: https://www.gooddata.com/trial/