Back to articles list
Data modeling isn’t a one-time activity. It is a continuous process. When building applications, developers use a version control system like Git to create multiple code versions while maintaining the history of changes and the hierarchy. Similarly, data modelers also need a feature that enables them to control the versions of their data models. That’s where SQL physical model versioning comes in handy.
Maybe your current data model is changing. Or maybe you’re migrating from one database to another by reverse engineering, recreating logical or physical diagrams, or translating DDL scripts. Whatever way you choose, Vertabelo enables you to do all this and more with features that support ER diagram and SQL database schema versioning.
How SQL Physical Model Versioning Works
Vertabelo has a version control feature that enables data modelers to save their modeling activity. You can do this by manually saving your diagram with the Save button or by letting the autosave feature create different saved versions automatically. Every autosaved model is stored as a version of the data model. As you’d imagine, it would take a fantastic amount of space to store a version every time you make a change. Hence, Vertabelo stores the version using the following rules:
- For the last 24 hours, every change is stored as a distinct version.
- For the last 7 days, the newest version from each hour is stored.
- For the last 30 days, the latest version from each day is stored.
- For models older than 38 days, only the most recent version from each week is stored.
This space optimization strategy for SQL physical model versioning is beneficial in two ways. First, less disk space is required to store the data model versions. Second – and possibly more importantly – it clears the clutter so that data modelers can concentrate on the crucial versions.
Working with Model Versions in Vertabelo
Vertabelo provides you with a lot of out-of-the-box solutions to work with your data models. The idea is to ensure that your time is spent thinking about and designing the model instead of on repeatable and automatable work. Let’s look at the Vertabelo features related to SQL physical model versioning, starting with figuring out where all the versions reside.
View the List of Model Versions
To see the list of versions, select the data model and go to the right panel. You’ll see the Activity, Details, and Versions tabs; select Versions. The default view will have the Show autosaved versions unselected, which means you’ll only be able to see the versions you have saved or tagged.
When you right-click on any data model version, a menu will prompt you to take one of the following actions on that data model version:
- Add or rename a tag.
- Preview the model version.
- Export the model version to XML.
- Create a clone of the model.
- Delete or restore the model version.
- Generate a migration script from the selected model version.
Below, you’ll see what Export to XML looks like after choosing one of the versions:
Tag a Model Version
To tag a model version, go to the list of versions and toggle the checkbox to include all versions. (The default view shows manually-saved and tagged versions only.) Pick any version; upon right-clicking, you’ll see a menu bar with an option to Assign tag. Once you select that option, you’ll be prompted to enter the name of the tag.
Tags are flexible. They can be indicate versions of the model (dev, test, prod, etc.) or to capture a new version’s changes (like a commit message in Git). You can create your own nomenclature for tags. In the image shown below, the latest version of the Flight Booking data model is tagged v1.1.
Create a New Model from a Model Version
Although Vertabelo’s version control doesn’t exactly support branches in the same way Git does, Vertabelo allows you to emulate that functionality. You can do this by creating a clone of any of the models in the version list. The clone will be automatically named, but you can change the name later. It will act as a standalone data model, with its own version control and history.
The following image shows a clone created using Create new model from this version:
Delete or Restore a Model Version
Vertabelo’s source control optimizations help you reduce clutter, but you can also manually remove versions that are no longer needed. To delete a version, go to the list of versions, right-click on the version you want to delete, and choose Delete version. Deleting a version will remove it from the list of versions.
On the other hand, if you want to set an old version as your current version, choose the Restore version option from the right-click menu. In the following image, you can see an example where an old version has been restored. Please note that any restored version is automatically tagged.
Generate a Migration Script
Vertabelo not only helps with ERD versioning, it also helps with SQL database schema versioning. You can use Vertabelo to generate various kinds of scripts – CREATE scripts, DROP scripts, and most importantly, migration scripts. Migration scripts are used to migrate from one version of a database to another. When deploying changes to an existing database, you’d usually want to migrate the current version of the data model to the new version.
Let’s say that you have tagged the current model v1.0 and the new version v1.1. You want to migrate to the more recent version. You will need DDL scripts to make the database changes necessary for the migration. The migration script will contain the SQL statements required to complete the said migration.
For any version in the list, select the Generate migration from this version option from the right-click menu. Selecting this option will take you to the screen shown in the image below, where you will be prompted to choose the source and target model versions. You can select from tagged, manually saved, and autosaved versions for migration. You can learn more about how to generate a migration script in Vertabelo on our blog.
Vertabelo Makes SQL Physical Model Versioning Simple
Data models change all the time, and every change potentially creates a new version. Vertabelo optimally handles SQL physical model versioning, providing many features to navigate the history of changes in your data models.
Apart from actions on individual model versions, Vertabelo also deals with migration between different data model versions. These features save data modelers a lot of time and routine tasks. To learn more about SQL physical model versioning, visit Version Control in the Vertabelo documentation.
