How to: Create MySQL Database in One Shot

This article gives step-by-step instruction for visual database creation.
There is a lot of instruments that allow database developers to avoid monotonous and error-prone manual writing of scripts for tables creation. But to have a complete picture while creating a database it’s necessary not only to speed up the process of database objects creation but also to visualize relations between them. dbForge Studio for MySQL offers a perfect tool for such purpose – Database Designer.

Let’s create the sample demo base database visually.

Task:

Create a database that would contain information about the salary of employees in different company departments.

Solution:

Let’s start with database normalization. In the result we will get three tables:

Now it’s time to use the visual tool for designing databases – MySQL Database Designer.
But before let’s set a connection to MySQL server

and create an empty salary_db database:

Now let’s create an empty database diagram file:

Press the New Table button on the toolbar:

Let’s specify the table name and database in the visual table editor that appeared. After this let’s create a column:

and fill its parameters:

After this let’s create all remaining columns in the same way, and after we’ve finished let’s click OK to save our first table.
Let’s create the remaining EMP and DEPT tables. Tables can be created in any order -the order does not have any influence.
Now let’s ensure that our database has referential integrity. To do this, let’s press the NEW RELATION button on the toolbar.
After performing this action the mouse pointer should change. Let’s click the DeptNo column of the EMP table and drag the relation to the DeptNo column of the DEPT table.
In the Foreign Key Properties that appeared press OK.

The result of these actions is that there is a relation between the tables now. Let’s add a relation between the SAL and EMP tables.
To do this, let’s drag a relation from the EmpNo column of the SAL table to the column with the same name of the EMP table. Note, that it is not necessary to press the New Relation button once more.
Now let’s arrange the tables of the created database for better perception. To do this, call the popup menu of the database diagram and select the Layout Diagram option.
And here is the result:

Now let’s generate and save the script of the created database for further usage.
To do this, select all tables and click Generate Schema Script in the popup menu.

And here is the result:

Conclusion

In the Database Designer tool, you see the database you are creating in the form of a database diagram. Such approach allows you to create databases of any complexity visually and save the script of the created database for future use.

Watch Tutorial

We also welcome you to take a look at our video tutorial for step-by-step instructions for creating a MySQL database with the help of dbForge Studio for MySQL: