There are many ways how you can store your data, but if you want to do it optimally you should use a database. Still, that doesn’t mean you can use it in a way you did it before, e.g. as text documents or sheets. You’ll need to build a data structure and to do it so you’ll need a data modeling tool. SqlDBM is one such tool and today we’ll take a closer look at some of its capabilities.
What are our expectations?
These days everything is in the cloud so that is also the case with SqlDBM. This will allow us to:
- Share our files with other team members
- Collaborate online
- Save our files in the cloud
- Store all versions of our data model in the same place.
An easy way to share files with other team members and collaboration in could reduce the time needed for storing different versions of our model and also reduces the risk to lose some work. Although obvious, this is one of the most important advantages of online data modeling tools compared to “offline” tools.
Besides these options, which are common for most web-based tools, we’ll need these that are specific for SqlDBM and which allow us to draw our data model.
The first thing you should do is, of course, to “Sign up” and confirm your registration. After that, you’ll gain access to SqlDBM and its features.
Now we’ll create our first project. To do it, we’ll need to click on the “Create project” button,
and then select the database/project type. I’ll select MySQL because this is the one I mostly work with but also the one that is widely used.
CHOOSE DATABASE TYPE
Differences between “Microsoft SQL Server” and “MySQL”, in terms of the database modeling logic, are minimal. Same rules are applied regarding table structure, relations, and other constraints, and there are differences in type names and properties. After we click on the “CREATE NEW PROJECT” button, the new screen with everything required for modeling shall appear.
Of course, we’ll need to select a name for our project.
In order to do anything in the middle area, we’ll need to use predefined tools/buttons. Some of the most commonly used are placed just about the modeling area. We can see them in the picture below.
Tools we have at disposal here are (from left to right): Undo, Redo, Selection, Area Selection, Reset Zoom, Zoom to Fit, Add Table, Identifying relationship, Non-identifying relationship, Add Note, and View mode. They are mostly self-explanatory so I won’t spend any words to describe them more detailed.
SqlDBM has a nicely designed UI and is in general very intuitive. Tools are placed just where you would expect them to, symbols used for these tools are well-known. This is very important because the time needed to get familiar with the new workspace is decreased significantly.
Few modeling tips
I always tend to apply the same or almost the same modeling rules in all of my database designs. These rules help me to read my model after some time, but also help others to read the model without the need to ask themselves any unnecessary questions. Of course, I haven’t invented these rules and you’ll find these patterns used by other designers too. So, these are “my” rules:
- Using naming convention – Always use similar rules when naming tables and columns, e.g. using only lower case letters and “_“. If the primary key is a single column, always name it id and if a column is a foreign key related, call it id_<name of the referenced table>.
- Using a single-column primary key, called id in all tables in the model. That column shall be of an integer data type with autoincrement property set.
- Setting alternate keys / unique values wherever that is possible.
Comments, always use comments. These could be comments related to database objects, but also textual notes that will not be generated in the code but are SqlDBM specific. Using comments and notes will increase readability of the whole model significantly.
Building a simple data model
We’ll try out the tool and create a very simple data model. We’ll create a structure needed to store a list of cites we’re interested in. To do it so, we’ll need two tables: the city table and the country table. Each country is uniquely defined by its country name while each city will be uniquely defined by its city name, postal code and the country it belongs to.
Our data model
Please notice that I’ve used a note to shortly describe the whole model
First, we’ll create both tables and insert all columns we need. It’s important to notice that columns are separated into two sections in the table, one for all columns used for the primary key and the other for all non-PK columns. In our case, we’ll have only the id column in the first part of the table. To make everything as we want it, we’ll need to set a few more properties.
To relate these two tables, we’ll use the Non-identifying relationship type. After selecting that icon we’ll click on the table which contains the primary key we need (country) and then on the table where we need that reference (city). Foreign key attribute shall be generated with a generic name and we can change that name to something more intuitive.
Properties can be related to tables, columns or any other object in our model. These properties are needed to achieve the desired functionality.
On the picture above, we can see how properties of the city table are set.
We’ve defined the alternate key (AK) city_unique as the combination of city_name, postal_code, and id_country. That becomes visible if we click on the city_unique in the Indexes section.
We’ve also set the Description property in order to describe the purpose of this table. While description itself doesn’t affect database logic, comments are extremely important and you should use them whenever you think something should be described.
There are also column related properties. On the picture above, we can see properties for the column id from the city table. You can easily see data type and well Auto Increment property checked.
Forward Engineer – Generate SQL
Forward Engineer is the real reason for all of this. While it’s great to have a graphical representation of the model, it’s even better if we can generate code out of that representation. Forward Engineer will do exactly that. Everything you’ve created graphically, from tables and relations, every property and even comments will be transformed into the SQL script.
Forward Engineer – Generate SQL & Reverse Engineer – Import existing database
After clicking on the Forward Engineer – Generate SQL, SqlDBM will ask us for all tables we want to create and do we want to include CREATE and DROP statements. I’ve selected all tables and all statements and the result is shown in the picture above. We can simply copy this script and execute it on our MySQL instance and whalla, we have our database.
Reverse Engineer – Import existing database
Reverse Engineer is expected to come soon. From this tool, we can expect that we’ll import our script and SqlDBM will generate a graphical representation of the model. Needless to say, how cool, but more importantly, how useful that feature will be.
Today we didn’t create a complicated data model, but rather went through steps we’ll use when creating our future models. Please consider this article as the start of our journey and feel free to tell us what else would you find useful. Can we add some options that will make you happy? Don’t hesitate to go with proposals because now is the best moment to do exactly that.