Back to articles list
A data model is more than just a pretty drawing that impresses users and stakeholders. When you add model validation to the design process, your data model can save you many hours of database-related work.
When you design a data model, your ultimate goal is for the model to become a functional database. However, your model is basically a drawing, while the database is a not-so-flexible structure that holds information. So if your model is flawed, those flaws will translate into serious problems that will make the database structure inefficient or just plain unusable.
Enter Live Model Validation, a feature embedded in Vertabelo’s data modeler that helps you prevent flaws from reaching the physical database. Live Model Validation is a helpful aid when you’re preparing or modifying a database model, one that could save you lots of work.
While working on your diagram, Live Model Validation lets you easily see any potential mistakes.
How Live Model Validation Works
While you work on the design of your data model in the Vertabelo platform, Live Model Validation keeps an eye on your work and takes note of the errors and problems it encounters. Don’t worry, it won’t interrupt your creative process; it works in the background, without you noticing. But whenever you’re ready for a reality check, it will show clearly if, where, and how you messed up. Then you will be able to fix these errors before running the SQL script against your database, enabling a smoother and quicker development process.
Logical and Physical Validation
With Live Model Validation, you can validate your database model at two levels: logical and physical. Which you choose depends on the type of model you’re working on. There are actually three different types of data models: conceptual, logical, and physical. The differences between them lie in their purposes, their levels of detail, and the phase of the development process to which they are applied. For more information, read how to implement a conceptual, logical, and physical data model in Vertabelo.
The Live Model Validation feature in Vertabelo applies to the logical and physical diagrams. Since the logical model deals with different kinds of objects than the physical model – entities instead of tables, attributes instead of columns, and so on – the validation process uses a different set of rules for each model. The rules for the logical model refer to the conceptual design, while those that apply to the physical model have more to do with the implementation of the model on a specific database engine.
As you work on a logical diagram in Vertabelo, the problems you will encounter will likely be things like entities lacking a unique identifier or objects with repeated names. Let’s see a few examples.
Live-validating the logical model.
In this database model, the Problems panel shows four issues that consist of three errors and one warning. (The Problems panel is located on the left side of the screen, next to the diagram area.) The three errors are two entities that have the same name – this actually accounts for two problems – and an entity with no attributes. The warning is about an entity with no primary identifier.
Let’s see another example.
Problems with default names and isolated associations.
When you create new objects in an ER diagram, Vertabelo assigns them default names, like “entity_1” or “association_1”. If you’re working against the clock, you could easily forget to change those default names to something more meaningful – realizing your oversight only when you look at the objects already created in the database. To prevent that, Live Validation will kindly warn you that you should change those names.
The example above highlights another problem with the association entity in a logical model. Associations stand in the middle of many-to-many relations; therefore, they must be linked with two tables, or the association is said to be isolated. This situation is highlighted as an error by Live Validation, so you have to correct it before generating an SQL script from your model.
Before you get too enthusiastic, don’t think that Live Validation is a silver bullet that will magically solve all your design flaws. There will be design problems that will pass through the validation process and you will have to put your wisdom to work in order to solve them. After all, you’re the designer, right?
Problems with the Physical Model
When you create physical diagrams in Vertabelo, Live Model Validation will highlight problems like data types that can’t be implemented in the target database engine or data type mismatches in related fields.
Consider the following model. Can you see what’s wrong?
Hint: take a look at the data types of the related columns.
With Live Validation, you don’t have to guess. Just have a glance at the Problems panel:
The Problems apnel shows what you’re doing wrong.
You can easily discover there’s an error in the fields involved in the relation between the tables: the column data types are different. To correct the error, simply double click on it and Vertabelo will take you to the relation in question so you can correct the column types.
Error Reporting
As we’ve seen in the examples above, you can check for detected issues whenever you like by looking at the Problems panel. The problems are classified into three groups: errors, warnings, and hints. Let’s see what each of these mean:
- Errors: These are serious problems that you must correct if you want your model to eventually become a database. You won’t be able to make any progress if, for example, you leave an entity with no attributes or a table without columns.
- Warnings: The problems shown in this section won’t stop you from creating a database from your model, but the data structure you will create will be far from perfect. For instance, you can create a table without a primary key, but you shouldn’t.
- Hints: These are suggestions to improve your design. For example, in some cases you might want to change the cardinality of a relation between entities.
Navigating Through Problems
If you have a really big ER diagram, with dozens of entities and relations, finding a particular object by browsing the diagram can be time consuming. The Problems panel knows that. That’s why it lets you navigate directly to a problematic object with just a double-click.
There’s a second Problems panel, also next to the diagram but on the right side of the working page, that shows only the problems related to the object selected in the diagram. (If no object is selected, it shows problems related to the model itself.) When you select an object, this panel changes to show its specific problems.
Validation and Reverse Engineering
Vertabelo lets you create an ER diagram by importing the objects from an existing database. This is especially useful to check a database for design errors. You can see the problems of the derived model and fix them in the diagram. Then you can create an SQL script and run it on your database to fix these problems, or you can fix the problems manually on the database and then repeat the import process to see if any problems remain.
When you need to refactor a data model, using Live Validation means you don’t have to worry about damaging the data model’s integrity. Any error you may introduce when refactoring will be immediately highlighted, together with a recommendation on how to fix it.
Fine-Tuning the Validation Process
All the problems detected by the validation process indicate flaws of varying severity that must be corrected to avoid potential inconsistencies or integrity issues once the model is transformed into a database. However, you may want to ignore certain kinds of problems at your own risk, knowing that they will not cause trouble because of how the database will be used. In this case, you can browse the Model Validation Settings (within the Model Properties panel, to the right of the diagram), and turn off the rules that you don’t want to be checked.
For example, if – for some odd reason I don’t want to know – you must create entities with no unique identifiers, you can turn off the rule that warns you when an entity doesn’t have an identifier ...
Turning off a logical model validation rule.
...and warnings about entities with no identifier will no longer show on the Problems panels.
The list of model validation settings varies according to the type of model you’re working on. When you’re working on a logical diagram, the settings include all the rules related to entities, attributes, relations, associations, inheritance, and the model as a whole. When you’re working with the physical diagram, you can turn on or off rules about tables, columns, keys, indexes, views, sequences, references, and checks.
Validating to Anticipate Errors
I cannot emphasize enough the importance of a thorough validation process when designing a database model. And if that process is automatic, better yet. I know many of you may not fully trust automatic tools and prefer the old-school, trained-eye validation process. The results of a “hand-made” validation process could be good enough, but – besides the much longer time it takes – it poses the risk of encountering errors far later on in the development process, when it’s too late and too costly to correct them.
