The Benefits of Data Modeling

Don’t put off until tomorrow what you can model today. Every minute you spend building and keeping your data model up to date means hours saved in the future.

In my early days as a database designer, I thought data modeling was an unnecessary step that only delayed the time to get the databases up and running. I thought that because, in most cases, databases started with just a few entities – 6 or 7, tops. “I don't need a tool to model that,” I thought. I built the model directly in my head, then dumped it into a physical schema without anything further.

Every time a database went live, I regretted my decision not to have built a data model from scratch. This is one of the lessons I have learned through years of data modeling. The number of tables grew, and the model became more and more complex until it was beyond my ability to represent it mentally.

At that point, I had no choice but to start building the model from the physical database (at that time, I did not have a database design tool like Vertabelo, which allows you to generate a model by reverse-engineering an existing database). As I struggled to build the data model from a database that evolved chaotically, I thought of all the effort I could have saved myself had I been more diligent from the very beginning – that is, had I put the data model together and enjoyed the benefits of data modeling.

Below, I share a list of the problems you can save yourself if you take the time to build a data model before you start creating tables in a physical database, contrary to what I did in my early days.

Manage and Compare Versions

There are many situations in which you want to go back in time, right up to the moment before making a big change in your database design. It may be because you realized the change was not right, or maybe the person who asked you to make the change regretted it and preferred that you left the database as it was before. You should be prepared to undo any change you make to a database for whatever reason.

If you always work with an up-to-date data model and all changes are made to the model rather than directly to the database, then you have a version history you can use to audit the changes made and roll them back if necessary. By linking your data models to a version control system such as Git, you can save a version of your data model every time it changes.

A version control system can even flag the differences between different versions of your model if you save it in a format that allows you to interpret the structure of your model. The DDL commands to create the physical model and an XML file representing the logical model are such examples.

With the Vertabelo design platform, you can generate XML files for logical or physical designs of your databases, or you can create SQL DDL files to generate complete schemas or just migration scripts. Since these documents are text files, they can be added to any version control system (Git, for example).

Vertabelo has its own built-in version control system that creates a version every time you save a diagram, whether you do it manually with the Save button or use the autosave feature. Versions can then be reviewed in a list and cloned to generate branches or variants of a model.

Database Migration

Migrating a database from one server to another can be quick and easy, or long and tedious. If you have an up-to-date data model, it is quick and easy. If you don’t, it is long and tedious.

One of the advantages of data modeling is that you can use your models to generate a DDL script to create the database structure on the new server. You get the peace of mind the script will respect that server’s version of SQL, even if it is a different version and incompatible with the current server. If you try to do this without a data model, you have to generate the DDL script in the SQL dialect of the source database, and then manually translate it to that of the new database. Be prepared to encounter errors of all kinds, and correct them as you go, as you struggle to create the new database.

Not only do the versions of a diagram created with the versioning feature in Vertabelo let you visualize the differences between two versions, but Vertabelo also generates the migration script automatically with just a couple of clicks. The resulting scripts of this process are error-free and have comments in the SQL code that allow you to identify each change easily. In addition, neat indentation and capitalization of the code make them easy to read.

The actual data migration is much easier if you use a data model to determine how and in what order to migrate the contents of each table. If you do not have a data model, you have to migrate blindly based on trial and error. When you try to migrate data from a table that depends on another table you have not yet migrated, you find out when an INSERT command throws a foreign key error.

Expose and Share Your Database Designs

If you are part of a team, you cannot keep your database designs to yourself. No matter how zealous you are with your creations, you must let your teammates see them. And even if you work alone, your clients or sponsors may ask to see the details of your databases.

To let others examine your databases, you can give them access to explore the objects they need to see and use – tables, views, diagrams, procedures, etc. But this has several drawbacks, such as database security management issues and everyone having to learn how to use database management tools. And if you do not manage database access properly, you can lose control of the modifications made to the objects of your design.

If you always work with an updated data model in a tool like Vertabelo, you can share it with clients or teammates who need to see and provide feedback on the design. Even multiple people can work on the model; you can manage permissions of each model to control who can see it and who can edit it. All this without requiring anyone to have access to the database itself!

Vertabelo lets you create a public link to the model so that whoever receives it can open it in read-only mode, or you can embed the diagram in a web page you own and publish that page for a client or co-worker to see.

Generate SQL Scripts to Update Databases

When you work directly with your database in a development environment and you make a change in a table, you may persist the changes without saving a script if you’re not careful. Then, when you have to take those changes into a testing or production environment, it becomes a lot of work to generate the scripts to reproduce the changes in different environments. Very often, you end up using schema comparison tools. While they can be very useful, you should consider them only as lifesavers in emergencies.

If you keep your data models in a design tool like Vertabelo, you can use its functionality to generate migration scripts without having to remember to generate a script for every change you make. You just have to tag a version of the model each time you make a move to testing or production – which you can do with just a couple of clicks. Then, you can make all the changes you want in the model, and the day you need to make a new release, you simply generate a migration script of everything you did since the last tagged version.

Document

Just as no one is exempt from paying taxes, we designers cannot neglect to document our databases. Documentation is necessary, not only for the formality but also to make the details of a database available to anyone who needs to see them without accessing the database directly, whether they are programmers, other designers, auditors, etc. Of course, the documentation must be up to date; otherwise, it is useless.

Creating database documentation without a data model is a tedious task that consumes too much time and effort. The task involves going through all the objects in the database and copying their properties, the details of the structure, their constraints, as well as the code of triggers, views, or other objects, all into a text document. By the time this is done, some objects may already have changed, and the documentation needs to be updated again.

With an updated data model and a proper design tool, documentation is no longer a chore that designers and developers shy away from. With a tool like Vertabelo, you can even generate complete documentation for your data models every time you make a change in any object to keep it updated. You won’t have to check the documentation; you can be sure it is thorough and complete.

The Benefits of Data Modeling: Keeping Things Under Control

The key benefit of database modeling is that it saves you time by helping you keep things under control. Databases are always growing throughout their life cycle, not only in the volume of data but also in the number of objects that make them up. Data models are the best tool to prevent them from becoming an unmanageable tangle.

The sooner you embrace data models, the sooner you can get a grip on your database. Remember the time you save on all these tasks, which – whether you like them or not – are part of the everyday work of a database designer.