Back to articles list
Let’s discuss some useful tools for designing and developing your database hassle free while saving you money and time.
In the modern software world, database systems play an important role. Most businesses depend on various database systems. Database architects and software engineers design and develop various types of database systems to manage the business. Online database tools have become very well known among database specialists and software engineers for their advantages.
The database design process is not just about drawing an ERD and converting it into a physical database; there is a wider process behind the database design process. That is why we need to use the right tools such as online data modelers, SQL editors, etc., for each phase of this process.
Design Your Concept: In this phase, you gather business and non-functional requirements through discussions with business teams, then you start to model your concept. You will have to go back and forth with the business on your conceptual model. For that reason, you need flexible tools to model your concept. Online data modeling tools are especially suited for the collaborative work with the business team required during this phase. I would like to suggest the following tools for your conceptual model:
Logical and Physical Data Modeling: Next, you will draw your logical and physical data models and present them to the software engineers. This too is a back-and-forth process between the data modeler and the software engineers who will influence each other’s work. To make this process smoother, I recommend a couple of tools that support easy collaboration, change management, version control, automation, etc.
Read more about the features to look for when selecting an ER diagram tool.
Create Your Physical Database: With modern online database tools, you have many features for creating your physical database from the physical data model without creating it from scratch. For this phase of continuous changes, I recommend the following tools which have many features to help make your project successful.
Create and Manage Your Physical Database: I recommend the following online tool for creating and managing your physical database.
Test Your SQL Queries: During your database designing process, having SQL tools, SQL editors, and online tools for testing SQL queries would be very useful, since we don’t always have the specific database engine at our disposal. There are many online SQL tools and SQL editors available in the market for testing our queries. We will discuss the features of a few of them.
Online Tools for Designing Your Concept
Vertabelo Online Data Modeler
The Vertabelo Data Modeler is one of the best available in the market. It is an online tool which makes many tasks easier than offline tools and provides two modeling options: logical and physical. You can use the logical data model option for your conceptual data model with less information, as shown in the ER diagram below in Vertabelo. Note that there is no clear line between the definitions of conceptual and logical data models, so data modelers can use them as they see fit.
Vertabelo comes with all of the required notations including crow’s foot notations, not just to model your concept but also to model your logical and physical data models. Because it is an online tool, it makes it easy to model your concept while interacting frequently with the business team. You can share your data model with your business team with the sharing feature in Vertabelo. Simply provide the email addresses of your business team members or of any others with whom you want to share the model, and click the button to specify one of the three different access levels: owner, editor, or viewer. You can also share your data model by emailing the public link to your business team members who do not have a Vertabelo account. Read more on collaboration in Vertabelo.
Furthermore, it has a version control feature with Git, with which you can manage the different versions of your model. To learn more about this, read How to Version Control Your Database with Vertabelo and Git.
Draw.io Online Diagramming Tool
Draw.io is a free online diagramming tool that supports drawing various diagrams including data models (ER diagrams). If you would like to draw your conceptual data model manually and from scratch, this is one of the best online database tools for you.
The Draw.io tool has many notations including crow’s foot notations for drawing all levels of data models including the conceptual ones.
Draw.io also provides a facility for collaborative work. You can save your diagrams in cloud storage (e.g., Google Drive, OneDrive, Dropbox, etc.) and share them with your team.
When you have a lot of interactions with your team and are changing your data model, Draw.io also provides a feature to keep track of the different versions of your model.
SqlDBM Online Data Modeler
SqlDBM is another database tool that comes as an online data modeler. You can create your conceptual data model in SqlDBM, and it provides many features for a smooth database concept design process which often involves many back-and-forth changes with the business.
SqlDBM also provides many notations, including crow’s foot and IDEF1X notations, for drawing all levels (conceptual, logical, and physical) of your data model.
This online tool makes your conceptual modeling process easy by making it a collaborative tool. You can share your models with your team by providing their emails. You allow them to edit using the check box.
In addition, you can use the Notes feature to record discussions, new requirements, requests for revisions against your model, etc.. This improves the accuracy of the information and saves you time and effort of recording the information separately using a different communication method.
Online Tools for Logical and Physical Data Modeling
Vertabelo Online Data Modeler
You have already created your conceptual diagram in the Vertabelo logical model option. You can improve the same model into a logical data model by adding more information to it as shown below.
When you are done with your logical model, Vertabelo can convert it into a physical data model that takes into account the exact database engine you are going to use. Vertabelo supports many database engines including MySQL, Microsoft SQL Server, Oracle, PostgreSQL, IBM DB2, HyperSQL, SQLite, Google Big Query, etc. When you create your physical data model from your logical one, Vertabelo validates your model against the selected database engine.
SqlDBM Online Data Modeler
You can improve your conceptual data model into logical and physical data models in SqlDBM. The tool has relevant features for editing your tables and relationships to add details (properties, columns, data types, primary keys, foreign keys, etc.).
The tool provides features for change management and version control to make the collaboration between the data modeler and the software engineer easier.
Online Tools for Creating Your Physical Database
Vertabelo Online Data Modeler
Vertabelo has a feature that generates SQL DDL files from your physical data model to create your database in the selected database engine.
It also provides 3 options for importing the existing database into the modeler.
- Live connection to the database. You can learn more about this in this article: “How to import an existing database into Vertabelo: Reverse Engineering.”
- Importing from SQL. If you have an SQL file with CREATE statements for the database, you can upload it to Vertabelo as explained here.
- Importing a Vertabelo XML file. If the older database was created in Vertabelo, you can import the Vertabelo XML file with the model definition.
Read more on the Vertabelo features in this article.
SqlDBM Online Data Modeler
SqlDBM also has a forward engineering feature for generating SQL DDL files to create your physical database.
It also provides a reverse engineering feature for importing an existing database into the modeler.
Online Tools for Creating and Managing Your Physical Database
phpMyAdmin
phpMyAdmin is a popular web-based database tool for MySQL and MariaDB. It is free and open-source. This tool should be installed on the server first before you can use it.
You don’t have to create your database from scratch in phpMyAdmin. You just copy the SQL DDL files created in your data modeler (e.g., in Vertabelo) in the SQL query editor and run it to create your physical database.
If you want, you can add tables and other components manually into your database. phpMyAdmin provides an intuitive web interface and many features including the ones mentioned below:
- Importing data from files in different formats.
- Exporting your database to different formats.
- Designer, a graphical option to design or edit your database.
This tool also comes with many other features for performing administrative tasks, managing user permissions, supporting foreign keys, etc.
Online Tools for Testing Your SQL Queries
SQL Fiddle
SQL Fiddle is an easy online SQL tool for testing and for sharing your database problems and their solutions.
SQL Fiddle lets you test and compare your queries against different database engines. You can easily switch between different database engines including MySQL, MS SQL Server, PostgreSQL, Oracle, and SQLite. With this feature, you can evaluate your queries against each environment very quickly.
You can simply copy and paste your SQL DDL queries in the Schema Panel and build your schema.
Then click on the Browser button to check your schema in the browser.
You can use the Query Panel to test your other queries (SQL statements like SELECT) on your schema as shown below.
Another useful feature of SQL Fiddle is that you can share your queries with anyone and with Q&A sites like StackOverflow. It’s as simple as copying the URL in your address bar and pasting it into your post, email, or any of your preferred communication methods. When you create a query, SQL Fiddle creates a unique URL. Others also can share their queries with you using the same method.
Because of these features in SQL Fiddle, you don’t have to bother with the installation of your database engine just for testing. You can easily and quickly evaluate your database schema with the SQL queries that your data modeler created or that you created manually.
SQLiteOnline
SQLiteOnline supports many database engines including SQLite, MariaDB, MySQL, PostgreSQL, MS SQL Server, etc. The tool has test servers to which you can connect for all of the supported database engines so that you can test your SQL queries.
You can simply connect to the appropriate database engine with a single mouse click, paste or enter your query in the query editor, and run the query to test. SQLiteOnline will do the rest for you.
Also, SQLiteOnline lets you connect to your remote database by clicking the Owner DB button and providing the required information for connecting to your database as shown below.
In addition, you can export your work from this tool into different formats such as CSV, XML, JSON, or SQL Schema. You can also import from different file formats such as SQL, JSON, and CSV.
DB Fiddle
DB Fiddle is also a useful SQL editor to support your SQL query testing process. It supports many versions of the database engines including MySQL, PostgreSQL, and SQLite.
You can simply copy and paste your SQL DDL files in the Schema SQL panel and create and test your SQL schema against any supported database engine. You will first have to select your database engine and version using the database engine drop-down list at the top left corner of the editor. Then, you can enter your queries in the Query SQL panel to test them.
The Results panel shows the results of your queries and displays any errors.
DB Fiddle supports collaborations with your team. This SQL tool is also particularly useful in testing queries when your database engine is not available to you.
What You Learned About Online Database Tools
For each phase of database development – data modeling (conceptual, logical, and physical), physical database development, and testing – there are many tools available in the market. Online tools have become more popular among database specialists, software engineers, as well as business teams, because their advanced features support collaborative work to save cost and time.
Data modeling and database development is not just about drawing an ER diagram. It is a critical process, and many business processes depend on it. If we do not choose the right tool for the right phase, we will end up with a failure.
It is important to choose tools that support a wide range of database engines and support change management, version controlling, automation, reverse and forward engineering, etc. For SQL tools and SQL editors especially for query testing, we should consider factors like ease of use, supported database engines, etc.
If you choose the right set of online database tools, you can make your next database project an interesting one for everyone.
