In Part 1 of this series, we successfully imported the SuiteCRM database structure into our online database modeling tool. That’s when we saw that the model contains 201 tables without relationships between them. We got a wild bunch of tables that looked really messy. In this article, I will show you how you can organize such a large model.Just after importing to Vertabelo , the SuiteCRM database model looks as follows:
Vertabelo offers you a feature to assigntagsto particular versions of your model. It may be used, for example, to match the versions of your database model with the versions of your application.To assign a tag to the current version of the model, clickSet tagin the options menu: Enter a tag name and clickSet tag. Now, this version is tagged. You can see this in theModel versionssection in the
Sometimes, you want the reference between two tables to reference an alternate key and not the primary key. Here’s how you can do it in Vertabelo .In this example, we want to model cars and their owners usingownerandcartables. Thecartable has an alternate key consisting of thevincolumn: We want theownertable to refer to thecartable, but using the car table’s alternate key. Let’s start with a typical reference by switching to a reference in the toolbox and putting a line from the
Vertabelo has a built-in mechanism of live validation that checks your model all the time and gives you hints on how to improve it. Every error, warning or tip is flagged by a dedicated icon that you cannot fail to notice.Within your workspace, the problems are indicated in two places. In the left panel you can review all problems found in your model, while on the right side you’ve got information on problems detected in the currently selected object only.
Do you know the quickest way to find a single table even in the largest diagram you can imagine? The answer is to double click the table’s name in the navigation tree!In theModel structurepanel on the left, you’ve got a navigation tree with groups of all elements used in your diagram, including database objects such as tables, references and views, as well as other subsidiary elements like subject areas and text notes.
Sometimes you may want to export your database model to a PDF file, especially for documentation purposes. It often happens that your diagram is larger than the default page size set in Vertabelo . If that’s the case, you should configure the printout options in theFormatsection.Make sure that no element in your diagram is selected. Go to theModel propertiespanel on the right and expand theFormatsection which provides some basic formatting parameters. Here you can configure the page size, orientation or margins of your printout:
If you need to automatically download SQL script from Vertabelo , for example, for the purposes of your build system, you definitely should check out ourVertabelo API. It allows you to download an SQL or XML of your model by a simple HTTP request. In the command line, it may look like this:To learn more aboutVertabelo API, go here .
In our previous Tip #16 and Tip #17 you could have learned that using table shortcuts makes it easier to create a well-laid-out database model. Do you know that references between shortcuts may be shown in the diagram too?Let’s go back to our example from Tip #16 . Assume that we have already created all necessary subject areas. Now, we want to put shortcuts of all purchase-related elements in thePurchasesubject area.First, select all relevant tables (use
In our previous Tip #16 , we showed you how to create shortcut tables using copy and paste. But did you know that you can create a shortcut of a table with only one move of your mouse? Just find the table in the navigation tree and then drag and drop it in the desired place in your diagram.Let’s assume that you want to create a reference line between two tables that are located quite far away from each other in your database diagram:
Yes, you can! Shortcuts combined with subject areas might completely change your approach to database modeling. They allow you to make your diagram much more structured, logically divided and readable. Let’s look at an example. At the beginning, we have a really standard database model for a shop.Open your database model in Vertabelo : Now, we’re going to use subject areas in the model. To start, let’s put the whole model in a subject area:
Large models often happen to be quite messy. Using subject areas, you can improve the readability of your model and make it easier to work with. The idea behind subject areas is to group tables inside them according to their purposes – it allows you to create some logical structure in your model and makes navigation through it more natural.Open your database model in Vertabelo : To create a subject area, choose
When it comes to sharing things like documents or photos with other people, sending files to each other is becoming less and less common – we usually prefer to send links. They are quick, convenient and neither use up disk space nor make a mess in our file system. At Vertabelo , we know how inconvenient it can be to send files and that’s why we created public links for models.
Probably you have already generated a PNG image for your model, but did you know that the image may be generated for chosen elements only?Open your database model in Vertabelo : Select the desired objects withCtrl + Clickor usingSelect area from the toolbox: ClickExport model as PNG imagein the top toolbar: And this is your PNG image: What’s more, if you select a subject area, everything inside it will be included in the image. Open a model containing a subject area:
From our previous Tip #2 you could learn how to move between columns in your table using just arrow keys. Now, we will show you how to use theDown arrowto create one or even more new columns. Probably, this is the fastest way of creating new columns in this part of the galaxy. You really have to learn this.Open your database model and select the table in which you want to create a new column. Go to the
Sometimes you may want to use a data type that is so new that Vertabelo doesn’t recognize it. In such cases, the application displays a warning that the data type is not supported. If you find these warnings too distracting, you can turn them off. In this post, we will show you how to do it.Let’s take a look at the following example.Assume that you have a database model for MySQL 5.5. Note that one of the columns uses the JSON data type which has only been introduced in
This one is frequently asked on our support: “In the data types panel, I can’t find the data type I need. Does this mean I cannot use it in Vertabelo ?”Of course, you can. Vertabelo will never let you get stuck in a data type dead end. Even if the type you need is not listed, it doesn’t mean you cannot use it. Data types you can find under the button are just the
Relationships in the real world may not be as easy as we would like them to be. Sometimes we want to model a situation where one table is refering to the other more than once. Luckily, with Vertabelo , this is a piece of cake.Let’s say we want to model a rental agreement between two people: landlord and tenant. In this situation, therental_agreementtable would need to refer to thepersontable twice – first for the landlord and second for the tenant. Your initial model could look as follows:
Sometimes the naming convention you use requires a specific pattern for the primary keys’ names. That’s why you may want to set your own names for each primary key in your database model instead of using default ones. Go on reading to get to know how to do this in Vertabelo .To give your own name to a primary key, select the table that contains the primary key you want to name:
You can comment on your database objects (tables, columns, views) in Vertabelo . You can also include this metadata in your database, where it can be accessed by your SQL developers or DBAs.Here’s how you can do it.Go to your database model: ClickSQL generation settingsin theModel propertiespanel on the right: Check theInclude comments for database objectsoption: Let’s try out how it works.Assume that you want to add your comment to the
Primary keys may contain more than one column. Multicolumn primary keys are frequently used for junction tables, which are used to model many-to-many relationships.Select a table: In theTable propertiespanel on the right, check all the columns that you want inside the key: Now, you have a multicolumn primary key created: You can check out your SQL code by clicking theSQL previewbutton in the top right corner:
Sometimes, you want all identifiers in your SQL script to be quoted. For example, you want them to have a particular case. Luckily, Vertabelo allows you to quote all SQL identifiers in your generated script to prevent you from any SQL name conflicts.Go to your model: Click theSQL generation settingstab in theModel propertiespanel on the right: CheckQuote all SQL identifiers: If you want to check out the result, select any table and click the
Sometimes there are columns in a table that don’t belong to primary key, but are still unique. To mark them as a unique, you have to create an alternate (unique) key containing it.Single-column alternate (unique) keySelect the table with the column you want to make a unique. Then, click theAlternate (unique) keytab in theTable propertiespanel on the right: ClickAdd key: Click thedown arrowto show the properties: Find the column you want to make a unique and add it to the key:
Did you know that you can copy tables between models in Vertabelo ? It’s useful for instance with tables that appear in almost every database model likeuser_account,address,clientorproduct, or with tables that are specific for your domain. You don’t have to create all these tables from scratch.Open the model which contains the tables you want to copy and click to select them. Note that you can hold down theCtrlkey if you want to select objects located in different parts of your model:
Would you like to make your database modeling faster and more convenient? Of course you would! One way to achieve this is to learn your database modeling tool’s keyboard shortcuts. To check them when working in Vertabelo , pressCtrl + I. Our favourite Vertabelo shortcutsDown arrow in the Columns sectionDid you know that you can move between columns in your table using arrow keys?
From today until Christmas Eve, we’re going to publish some tips & tricks for Vertabelo . Check our blog every day for a new one!List of all available tips: How to move a column up or down Use keyboard shortcuts in Vertabelo! How to copy tables between models How to make a column unique How to quote table names in generated SQL script How to create a multicolumn primary key
Have you ever changed your mind about the order of columns in your table? You probably have! Fortunately, changing the order of columns in Vertabelo is as easy as pie.Select the table that contains the column you want to move up or down. InTable propertieson the right, you can see a list of the columns. On the left of each column’s name, there is acolumn selector: Find the column you want to move, click on its
Even though Vertabelo doesn’t provide a direct conversion of a data model from one database engine to another, this can be easily done in a few simple steps.The user’s questionWe’ve been developing a web application for a while. Initially, we wanted to use MySQL for our database but finally we decided to choose Postgres. Unfortunately, the entire data model was created for MySQL. How can we quickly migrate it to the new database type? Is there a simple way to do this in Vertabelo?
Book and Author Importance of Data Model Quality Takeoff Checklist Merciless Review Merciless Humiliation? Is it Agile? Conclusion Book and Author Today I’m going to review“A Check List for Doing Data Model Design Reviews”by Kent Graziano. This publication is available as an e-book on Amazon.com .The book is very short – it will take you less than an hour to read it. But don’t let the small volume mislead you. Graziano’s writing is very concise and full of information. There are no digressions, examples, anecdotes or metaphors. Just raw, austere instruction on what to check when designing a data model and why it is worthwhile to have the whole team review it before moving to the next steps of implementation. Even the title of the book is like the book itself – clear, not very attractive, but concise.
Tell us what you want!If you have your own ideas for new features or you think that something could be improved in our application, do not hesitate to share your opinion. Send your suggestions to us via our Support Service . There, you can upvote or downvote new feature requests placed by other users. That way, you can have a significant impact on the development of our application. Go to Vertabelo’s Support Service »
If you’ve ever had to design a database model with hundreds of tables, views and references, you know very well how difficult is to make such huge diagram readable and comprehensible. One of the possible solutions is to use some colors to distinguish different groups of tables or subject areas. See how Vertabelo allows you to do this quick and easy.To illustrate how you can color your entity relationship diagram to increase its readability, we will use a sample uncolored database model for a simple online store:
What is jOOQ? jOOQ, which stands for “jOOQ Object Oriented Querying,” is an ORM (object-relational mapping) for Java. With jOOQ you don’t write statements with “SQL-like language”; like Hibernate’s HQL. Instead, you write well designed and intuitive DSL code which, for someone speaking SQL, is obvious and self-explanatory. Read more » Some time ago I announced that the integration of Vertabelo and jOOQ was coming soon. While we’re waiting for an official (stable)
In this article, I’ll make a comparison of two popular database modelers: Vertabelo in version 1.21.0 and Navicat Data Modeler in version 1.0.12.Features are listed in the left-hand column and each tool’s feature support is identified under the appropriate heading.
We hadn’t planned on adding a feature to print diagrams. Our original idea was that Vertabelo would supersede paper diagrams. Nevertheless, some of our users pointed out that they rely heavily on printouts ( Export to PDF topic). So, I’d like to announce a new feature called “Export to PDF” also known as “Printing.”Note that exporting a database model to a PDF file is available for Premium and Team account plans only (see the comparison of the plans’ features in our
In this article, I will compare two online database modelers: Vertabelo and Sea Quail Database Diagram Tool. Here is a matrix of each tool’s key features and functionality.Compared versions: Vertabelo 1.14.3, Sea Quail v.32.
You may not believe it but the items in this picture are not just kids’ toys; they areimportant partsof the software development process! We call them “database tokens.”When there are multiple developers on the project and you are not creating a few java classes for yourself, you need a versioning system which stores files with the source code. We use GIT, which allows developers towork simultaneouslyon the same module or even on the same Java file.