Data modeling is usually seen as a visual activity, but many data modelers prefer writing a SQL script to generate the data model. Catering to different teams and different ways of collaboration between teams, Vertabelo offers you three different options to create your data models. You can create a physical data model, a logical data model, or a SQL script. Creating a physical data model or a logical data model consists of a GUI-based approach.
The logical model is a visual representation of a rough idea of what the entities, relationships, and attributes in a model will be. The physical model, also a visual representation of the data model, is a stricter idea of the logical model where you have defined data types, constraints, and other things based on the database engine you will be using.
A SQL script is what you get if you want to see a physical model in text alone. A SQL script, on the other hand, is a textual representation of the data model. As mentioned above, Vertabelo provides you a way of working with all these three options. In this tutorial, we will look at the third option — working with a SQL script.
Creating a SQL Script
You can use this option when you want to write your DDL from scratch by using the Create new document in the upper panel of the Vertabelo dashboard as shown below:
Once you click on the SQL Script option from the New document window, you land on the following screen where you are asked to name your SQL script.
You can also upload existing SQL scripts into Vertabelo by using the Upload SQL scripts as shown below:
The upload SQL scripts feature can be very useful when you are migrating your data models to Vertabelo. It is also very useful if you haven’t used a data modeling tool for creating data models before. You can export your existing database schema from your database’s SQL client and import it into Vertabelo. On a separate note, you can also use Vertabelo to export your current physical models (ER diagrams) to generate SQL scripts.
After creating a new document or uploading an existing document, you can access the document by opening the SQL script in the SQL script editor as shown below:
The SQL script editor has syntax highlighting built-in. You can easily differentiate SQL keywords. It serves two purposes. First, it helps you format the DDL better, and second, it helps you easily identify any keyword-related syntax errors.
Working with the SQL Script
After creating the file, you can use the upper panel to perform the following operations on the file:
1. Share this model with your team
Pressing the Copy option duplicates the SQL script document within the same folder. If your document is called SQL Script, Vertabelo will create another document with the name — Copy of SQL Script.
A common use of these SQL scripts is storing them in an external version control system or deploying them on your local for testing and development. You can use this option to download the scripts to your local machine and work with them.
You can use this option to rename the SQL script. This option comes in handy when you have to
5. Move to
This option gives you another way of organizing and reorganizing the SQL scripts in your project. Using the Move to option, you can move a SQL script from one folder to another, as shown below:
If you have created a SQL script by mistake or your SQL script has served its purpose, you can go ahead and remove it from your documents using the Delete option as shown below:
7. Switch between SQL scripts
While working on a data modeling project, you may organize your SQL scripts in different files and folders. To switch between different SQL scripts, you can use the left-most drop-down while working with a SQL script. It will show you the list of SQL scripts in your account, and you can switch to any script.
Vertabelo’s autosave feature ensures that you don’t lose any last-minute changes you made to your SQL script when you switch between scripts.
Please note that all the options available in the upper panel that enable you to work with your SQL scripts are also available as options in a floating drop-down menu that comes up when you right-click on a given script.