For some people, albeit technically inclined, there may be no clear line separating conceptual, logical, and physical database modeling. This article explains what database modeling is, and provides a clear description of the different database modeling types.

Please note that in this article, the terms, “database model” and “data model” and their respective variants are used interchangeably.

What Is Database Modeling?

Often presented visually by means of a diagram, a database model is a representation of a database showing its design and structure and the manner in which data can be stored, organized, accessed and manipulated. It aids in the enforcement of business policies and compliance with government regulations and policies on data handling. It also ensures that database developers are consistent with things like naming conventions and default values for database records.

Graphical Representations Of Database Models

As earlier mentioned, database modeling is usually done by means of diagrams. There are many types of diagrams used in database modeling including:

• Business Process Modeling Notation
• UML Diagrams
• Flowchart Technique
• Entity Relationships Diagrams (ERDs)
• Data Flow Diagrams
• Role Activity Diagrams
• Role Interaction Diagrams
• Gantt Charts and others

However, of the above, UML Diagrams and Entity Relationships Diagrams (ERDs) stand out.

UML Diagrams

UML Diagrams are used primarily for specification, visualization, development, and software documentation. Boasting 14 different types, it possesses so much flexibility, making it useful for the visualization of almost any business process.

Some techies say UML Diagrams are the object-oriented version of flowcharts. On the other hand, others find it confusing because of its many variants.

Entity Relationship Diagrams (ERDs)

An Entity Relationship Diagram, otherwise known as an Entity-Relationship Model, is a graphical representation of entities (like people, objects, events, places, etc.) and how they relate to each other. They are often used in computing to visually demonstrate/represent data within databases or information systems. ERDs are perfect for relational database modeling.

Why Model A Database?

Here are some reasons why you should model your data before building your database.

• Database models document important concepts, object relationships, and technical jargon. This provides developers a basis for long-term maintenance.
• Modeling cuts down or eliminates data errors and even application errors. Compared to actual databases or completed application code, database models are easy to review. And such reviews quickly help identify problem areas or potentially buggy scenarios. That way, even before code development begins, some errors can be worked out beforehand and developers would be helped to write code that contains fewer application errors.
• Database models help to identify missing and/or redundant data.
• Although the initial creation of a data model is laborious and may be time-consuming, it saves costs on upgrades and maintenance in the long run.
• A data model can be used to estimate the complexity of software, and gain insight into the level of development effort and project risk.

So, yes, data models are indispensable for the creation of high-quality, and low-maintenance software.

Types Of Database Models

Having understood what database modeling is and seen the benefits thereof, let us now discuss the different types of database models, namely, conceptual, logical, and physical.

Conceptual Data Modeling

This data model aims at defining what the proposed system will contain. Often used at a high level by business stakeholders and data architects, its purpose is to organize, scope, and define business concepts and rules. The primary focus of this model is to establish the entities, their attributes, and the relationships between them.

Like the name implies, at least, at the most basic level, conceptual database modeling deals with broad concepts, so it should be no surprise that this model presents little or no detail about the actual structure of the proposed database.

In conceptual database modeling, there are three basic concepts, named entities, attributes, and relationships.

An entity is the data equivalent of a real-life thing, like a car. An attribute is a characteristic of that real-world object, like its color or weight, while a relationship is an association between two entities.

So, assuming there are two different car records in a database table, and both cars were sold the same day, one relationship between them would be the fact that they have been sold. And the date on which they were sold would be the same as well.

Characteristics Of Conceptual Modeling

Conceptual data modeling is characterized by its…

• being targeted at business audiences
• focus on representing data as it is seen in the real world
• being independent on hardware specifications and
• ability to cover organization-wide business concepts

Logical Database Modeling

When the task at hand is to compile business requirements and present these as a model, the logical model comes handy. Very often, the information that is gathered has to do with business processes, organizational units and entities.

Once all of this is gathered, diagrams and reports are made, in the form of User Feedback Documentation, Entity Relationship Diagrams (see above), and Business Process Diagrams, which illustrate processes in the systems.

These reports and diagrams give the development team an idea of how data should flow within the completed system in the organization and illustrates the activities of the users of the systems. With this information, developers can determine how to physically design the database and the corresponding software application.

Logical modeling precedes physical modeling and ends when it is reviewed by developers, management, and finally the end-users to see if more information needs to be gathered. The purpose of this model is to create a sort of technical map of business concepts and rules.

Physical Database Modeling

If you read through the discussion of the preceding models carefully, you would have noticed that they are not directly involved in the actual design of the database. As their names imply, they are conceptual and logical.

This one, however, is physical. It doesn’t get rawer than this!

A physical database model deals with the actual design of the database, taking into cognizance the specific relational database system to be used for the final development of the database. All the information gathered during logical modeling is converted into relational and business models.

Data objects like tables and columns come into the picture, incorporating such elements as foreign keys, primary keys, unique keys, and indexes to provide constraints for creating, accessing, and manipulating records.

In addition to tables, views can be designed to summarize data from tables or to provide an alternative perspective from which specific bits of data can be viewed and analyzed.

Characteristics Of A Physical Database Model

• The physical data model describes data needed for a project and how it is to be stored at a very low level.
• It is made for a specific version of a database management system, data storage system, or technology to be used in a specific project.
• It contains relationships between data objects.
• Properties of table columns like data type, length of data stored therein, and default values of the columns are defined.
• Primary keys, foreign keys, views, indexes, access profiles, and other specific elements of the tables are defined.

Conclusion

A simple way to think of these database modeling types is this:

• Conceptual database modeling is data modeling done at a very high level by project stakeholders and architects. Unlike physical database modeling, conceptual modeling does NOT contain low-level, granular information.
• Physical modeling is used for actual database design. Nothing high level here. This is pure low-level goodness. Almost as beautiful as raw code!
• Logical database modeling sits somewhere in-between conceptual and physical database modeling.

Even though I have had to build logical database models (and even conceptual data models!) while employed in core software developer capacities in the past, I like to view logical database modeling as a Business Analyst’s responsibility.

Yes, you can build an application without all the hassle of creating various data models. But for large applications, detailed data modeling should not be overlooked. It makes it easier for developers to carry out upgrades and maintenance on applications in the future and saves costs in the long run.