Create logical data model documents, physical data model documents, and SQL script documents using Vertabelo.
Vertabelo allows you to create and import your models with three different types of documents. This article takes you through all three Vertabelo document types: the logical data model, the physical data model, and the SQL script.
These documents let you start modeling from scratch or import your existing progress into Vertabelo. The different representations of the same data model help a variety of people understand the model. From engineers to business users, people of varying comfort levels in data modeling can see how models are structured.
Vertabelo Document Types
As mentioned, there are three types of Vertabelo documents:
- Logical Data Model. Use this document type to create a database-agnostic data model. You can find more information about logical data models on our blog.
- Physical Data Model. Use this document type to create a data model for a specific database, such as Oracle, MySQL, PostgreSQL, SQLite, IBM DB2, Microsoft SQL Server, HyperSQL, and Amazon Redshift.
- SQL Script. Use this document type to store any kind of SQL script, especially DDL scripts you can use later to create the physical data model.
To see the different types of documents, create a folder called “Document Types.” When you navigate to the folder by double-clicking on it, you see the following menu:
Use the “New document” option to create a document. When you click on “New document,” you see the following screen where you can choose from one of the three options.
1. Logical Data Model
Logical diagrams or data models are database-agnostic visual representations of the data model. You can use a logical data model to draw up the first version of the data model even before choosing what database to use with your application. This decouples logical data modeling from the choice of architecture.
Logical data models in Vertabelo are created by using standard ER diagram notations and data types. Vertabelo can later map these data types to any supported database. You can do this by generating a physical data model from a logical data model.
When you choose the “Logical data model” option from the choice card shown above, you see the following screen:
In the following image, you’ll see a logical data model created based on this open-source DDL script for creating a Reddit-like forum.
2. Physical Data Model
While creating a physical data model, you are asked to choose a database engine along with the source for the physical data model. You can also create a physical data model by converting an existing logical diagram to a physical diagram.
To create a new physical data model, enter the name of the model, choose the database engine, and specify the source of the data model definition. In the following screenshot, notice how you can create a physical data model from an example schema defined in Vertabelo:
When you press the “Start modeling” butt on, you see the following in your Model Editor window. You can edit this model, add documentation, and redefine the relationships to experiment:
Alternatively, you can choose from one of the following options to create a physical data model:
- Empty. This option is useful when you want to create a physical data model completely from scratch.
- From SQL. You can use this option to create a physical data model by importing a DDL script.
- From XML. Similar to the previous option, you can create a physical data model by importing an XML database definition specification.
Using the import functionality, you can use an existing DDL script to create your physical model as shown below:
3. SQL Script
Finally, using the third option, you can create a SQL script document in Vertabelo. This gives you a blank sheet for writing a SQL script from scratch or for copying and pasting your existing SQL script into a Vertabelo document. You can create a new SQL script document as shown below:
Vertabelo offers additional ways of creating SQL scripts. You can also create a SQL script directly from a physical data model as shown in the image below:
Generating a SQL script from a physical data model results in a file created in the same folder. When you open the file, you see a DDL script for your physical data model as shown below:
Note that the DDL script says Vertabelo has created it.
Use the Different Vertabelo Documents to Your Advantage
The three Vertabelo document types allow you to create and edit data models from several perspectives. If you’re a business user and want to understand the data model without many implementation details, a logical data diagram may make the most sense for you. If you’re a developer or a data modeler who needs to follow data model specifications, a physical data model or a SQL script may be the one for you. Choose the one that makes the most sense for what you need.