Designing and creating a MySQL database online using QuickDBD

Why shouldn’t you ask a Database Administrator to help you move your things?

They’ve been known to drop tables!
A SQL Query walks into a bar… In one corner there are two tables.
The Query encourages himself, walks up and asks:
Mind if I JOIN you?

Jokes like those are funny to people which understand the databases concepts. If you don’t understand these jokes and you are interested in the databases it’s time for learning. Nowadays, there are many free online tutorials, and learning of new things was never easier, especially learning of software skills… When you become familiar with some basics about relational databases and SQL (Structured Query Language) you need to test your knowledge in some database management system.

One of the world’s most widely used open-source relational database management system (RDBMS) is MySQL. As such, MySQL is a good starting point for beginners, but it’s also been used for some advanced solutions. Many big companies/organizations are using MySQL, such as Facebook, YouTube, Twitter, NASA, Tesla, Uber…

The First step is to choose the purpose of the database and to make MySQL database design. As “learning” is already mentioned let’s create a simple database for students who will attend (database) courses.

But, how to design MySQL database? Where to start? It’s certainly better (especially for beginners) to use MySQL visual database design…

A simple answer to this questions is the free online tool called QuickDBD (Quick Database Diagrams). It makes databases creation easy for many different platforms and it can be used for MySQL database design.

In next lines of this article, it will be described how to use QuickDBD tool as MySQL modeler for a database “Courses for Students”.

Designing the database in QuickDBD

Good news is that you don’t have to install anything! Just open QuickDBD website and then click on “Try the App”:

*On QuickDBD website you can find out more about it*

On loading QuickDBD application you can take a quick tour that will show you where you can define your schema, where is the position of a diagram, and where you can get more documentation about QuickDBD including FAQ and video tutorial.

You can take a look at the Sample diagram which is loaded by default. But we are here to create the new one, so, go to menu item File / Clear to start creating MySQL visual database design for our example, “Courses for Students”.

Trying of the app is completely free, but also, you can sign-up for a Basic account (also free) or Pro (with weekly / monthly / yearly subscription). To create a new account, you only need to enter valid e-mail and password or you can use existing Google / Facebook / Twitter / Microsoft / GitHub account to log-in. It will enable you to save and continue your work.

Designing the database for Courses for Students

It will be shown how to create 4 tables for database “Courses for Students”. Here is the description of them:

Student – for data about students that will take the courses
Course – every course will have students and it’s lessons, description, duration…
Lesson – lessons that will be lectured by tutors
Tutor – for record of tutors, professors, lecturers…

Now you can create MySQL database design by typing. It’s pretty quick:

When you type the name of the table Student it will appear on the right side. To add fields to this table add a new line and type – (dash). After “-“ sign, every text in next lines will be field until an empty new line is added. So type in name of the fields and it’s data types. To add Primary Key to the table simply write PK next to the field type. Making relations and foreign keys will be explained in the further text of the article.

It’s recommended to type code (to get a better understanding of QuickDBD) even you can just paste code like this:

Student - StudentID int PK Name varchar(30) DepartmentID int Adress varchar(50) DateOfBirth date

Course - CourseID int PK StudentID int LessonID int Description varchar(50) StartDate date EndDate date

Lesson - LessonID int PK Description varchar(30) TutorID int

Tutor - TutorID int PK Name varchar(30) Title varchar(30)

The result of this code are tables:

You can rearrange the position of tables by moving them by mouse. Once, you move them you need to make relationships. Just drag from point-to-point (from the field that will be foreign keys over to the field that is the primary key). It’s the simplest way to create MySQL relationships.

*Move tables and make the relationships*

Exporting the Create script

You’ve created tables, relations and you have a database diagram. In two clicks you can create the script that is valid for MySQL. Just choose menu item Export / MySQL/MariaDB, and QuickDBD will prepare the create script in your Downloads.

Here is the created script for tables: QuickDBD-Example

How simple is that! You don’t need to bother with MySQL syntax QuickDBD does a coding for you. If you a need to do some modifications (to add fields, change data types, relations…) you shouldn’t modify that file. Just use QuickDBD and after finishing modifications export to MySQL code again.

Trying the code in MySQL

QuickDBD generated the code, so let’s try to create tables in MySQL.

In order to maintain good practice and avoid to install any software on your computer, we should look up for a service that offers free online MySQL database engine.

Paiza Cloud offers cloud IDE solutions and it supports MySQL online editor. Like QuickDBD, Paiza Cloud doesn’t require registering, installing anything, setting environment… Just open: https://paiza.io/en/projects/new?language=mysql in your browser and you are ready to write MySQL code. You can paste the downloaded code or drag and drop it to editor (it will appear in new tab):

*Simply Drag and drop downloaded code and run it*

Execution worked and you have the basic example of online MySQL database “Courses for Students”. The database is ready to be filled in with data.

Conclusion

Using QuickDBD as MySQL database schema generator is an excellent choice. Except for MySQL modeler, it can be used to model database and create code for SQL Server, PostgreSQL, Oracle (12 c+)…

Little tip: Founder of MySQL, Michael “Monty” Widenius is now developing MariaDB the fastest growing Open Source database in the industry. The code generated by QuickDBD for MySQL is also compatible with MariaDB.

Whether you are a beginner (who starts to learn about databases) or an experienced professional you need to consider QuickDBD tool.