Data models are frequently used in database design to represent the informational items stored in the systems and their relationships. They offer database teams a method of abstractly planning how the system will look before embarking on its development. The time constraints put on teams working in dynamic, multi-platform environments require them to use their time as productively as possible, and data models can prove very valuable in streamlining database development and migration.
The benefits of using data models in the initial phases of database development can be felt throughout the process and often lead to a more optimized and high-performing system. While you can design databases without using data models, there are several reasons why it makes sense to make use of them. The most impactful is the ease with which data models can be reviewed and changed in comparison with a database that has already been designed and constructed.
Making the modifications necessary to realize business requirements is facilitated through the use of data models. Details can be fleshed out before engaging in constructing the database. Insights obtained in the modeling phase can influence the database platform chosen to host the final product. It may become evident while investigating data elements that are incorporated into the model that a particular solution will work better than others.
Three Basic Types of Data Models
There are three basic types of data models used when designing or migrating databases. They can be used sequentially to fine-tune the way information will be laid out in the finished system. The models coincide with the mental processes that we consciously or unconsciously use when constructing and designing complex structures. Using the models helps teams take an idea and turn it into a viable database or application that addresses business requirements.
Conceptual data models
The purpose of a conceptual data model is to provide an abstract summary of a database project that can be disseminated and understood by all associated business stakeholders. It’s the first step in the modeling process and begins to take the rough ideas behind the database and give them substance and form. Here are some of the characteristics of a conceptual data model.
- They are designed for a business audience.
- The focus is on enterprise-wide business concepts.
- Definitions are provided for all entities in the model.
- A limited number of entities are described with few or no attributes.
- Conceptual models are developed independently of any specific database or technological solution.
Logical data models
Logical data models are often constructed from a conceptual model and are also created with no determination on the specific database solution that will be implemented. Data requirements are described from the perspective of the needs of the business. Logical models often share these common characteristics.
- The models focus on the requirements of a particular project.
- The number of entities is increased from that of the conceptual model as data elements are refined.
- Relationships between entities addressing cardinality and nullability are part of the model.
- Data elements are typed with their lengths and the degree of precision defined.
- Entities and attributes are more fully defined than in a conceptual model.
Physical data models
A physical data model is developed for a specific database platform. It further defines the way the database or solution will be implemented and is the culmination of design features identified in the conceptual and logical models. Physical models include the following characteristics.
- Physical models describe the data requirements for a single project or portion of an application.
- Tables and their relationships are typically included in a physical model.
- Tables and columns are fully defined.
- Datatypes for columns are specified along with their precision and length.
- Additional physical objects are incorporated into the model including key constraints, indexes, store procedures, and security roles.
The journey from a conceptual model through the logical and physical models is a proven method of designing a database that meets business requirements.
Tools for Data Modeling
Tools specifically designed for data modeling make it much easier for teams to model productively. IDERA’s ER/Studio Data Architect is a full-featured data modeling tool that supports a wide range of database platforms. This makes it the perfect application for creating models to be used when migrating between database platforms, a practice often called for in diverse environments.
ER/Studio Data Architect can be used to create physical data models from existing databases that are instrumental when merging or migrating systems. The tool’s repository enables universal mapping between conceptual, logical, and physical database models. Naming standards can be assigned and automatically applied to logical and physical models. Model complete validation ensures that your models are not missing anything important or contain unnecessary elements such as duplicate indexes.
An IDERA whitepaper that concentrates on star schema and normalized relational database models is available and highly recommended if you want a more thorough understanding concerning their use. The paper talks about the differences in these two popular types of models and provides some guidance on which models are best suited for particular systems. Give it a read if you are interested in data modeling.