Our team recently completed a solution assessment document to get project sign-off for a local client. In an effort to consolidate and document our recommendations, we pulled together a group of resources with a variety of business and technical backgrounds. Our primary output was a traditional text document, but we quickly realized we could more effectively communicate our findings with a supporting illustration or model.
When defining what our model would communicate and what it would be called, we identified a variety of model types and interpretations that could be used to simplify and represent a complex process. The most effective model for a situation depends on the subject matter, the subject area, and the audience. Given the situation of designing a data solution, we contemplated using a conceptual model, a logical model, or a physical model. After several conversations, we determined a “conceptual model” best served the purpose of our assessment.
So what is the difference between these three models? I’ve highlighted the key attributes and differences below along with examples to help give you a better understanding of the three model types and when to use them.
The conceptual model is used to communicate the fundamental concept and purpose of the solution. It enhances the understanding of the system it represents. Typically, the conceptual model is used in a diagram format to show data flows, processes or relationships of a current or future process for presenting ideas at a high level. It includes documentation for the future state or an overview for the current state. Both technical and non-technical audiences reference this model to understand the solution.
Like the conceptual model, the logical model gives an abstract representation of the data flows, inputs, and outputs of the system. Although, it goes deeper into the technical areas, the goal is to keep the model technology agnostic. For example, the process of data movement in a logical model is given a generic label of ETL along with entity names, relationships, attributes, and cardinality. Logical models take the conceptual model to a more detailed level by showing how the solution could be implemented.
The physical model is used for construction. It is the model that shows how the solution will be implemented. It builds off of the logical model by adding additional details including the technology platform that will be used. The actual processes identified are conveyed. In comparison with the logical model, source and destination systems, databases, tables and columns are named. In addition, the entity names are converted to actual tables names, attributes become physical fields with defined data types, and relationships with primary and foreign keys are documented.
When taking everything into consideration, the model used is a result of what needs to be communicated to the stakeholders, developers, and business analysts. Each model has its strengths and application for the given situation. As my team learned, choosing the correct model is essential to convey the appropriate information to the proper audience.