Advertisement
Databases

Visual Database Creation with MySQL Workbench

by

In today's tutorial, you'll learn how to use a visual database modeling utility to draw a database diagram and automatically generate SQL. Specifically, we'll review how to use MySQL Workbench, a cross-platform, visual database design tool.


What is MySQL Workbench?

MySQL Workbench is a powerful tool developed by MySQL with three primary areas of functionality:

  • SQL Development: Replaces MySQL query browser. Allows the user to connect to an existing database and edit and execute SQL queries.
  • Data Modeling: Complete visual database design and modeling.
  • Database Administration: Replaces MySQL administrator. Graphic interface to start/stop servers, create user accounts, edit configuration files, etc.

In this tutorial, we'll focus on the Data Modeling aspect to create a database from scratch, and then have just a quick look at the SQL editor to execute our generated SQL script and create the database within MySQL.

MySQL Workbench is available for Windows, Linux and Mac OSX. There are two different editions: the Community OSS Edition and the commercial Standard Edition. The community edition is Open Source and GPL licensed, as you'd expect. It's fully functional, and is the one we'll be using in this article. The commercial edition adds some extra functionalities, such as schema and model validation or documentation generation.

Note: this tutorial is based on the Community OSS Edition version 5.2 (5.2.16), currently in beta release at the time of the writing (April 2010).


Planning our Database

To learn how to use MySQL Workbench, we'll use a very simple database for online classes as an example. Suppose a group of teachers want to offer online classes for several subjects, using Skype or any other video conferencing software. For our little project, we have decided that we need to store the following information:

When drawing our diagram, we will need to know the relationships between these groups of data as well; so we better think about that now!

  • One teacher can teach many subjects
  • One subject can be taught by many teachers
  • Each class has only one teacher
  • One teacher can teach many classes
  • One student can attend many classes
  • One class has many students
  • One class may have several hours (in a week)
  • At one particular day and hour, there may be several classes
  • A class is about one subject
  • One subject may be taught in many classes

At this point, we have all the information we need to meet the star of this show...


Send in MySQL Workbench

It's time to launch Workbench. In the data modeling part of the home screen, we click 'Create new EER Model', and the following screen appears:

When we create a new database model, it contains the default mydb schema. We can rename it and use it as our DB schema. A database model can have several different schemas.

The catalog on the right will show every element in our schema, and allow us to drag and drop elements to diagrams if needed.

Having the separate sections for Physical Schemata and EER Diagrams, and the possibility to include several Schemas in one database model may be confusing. The next section explains these concepts and how they are related.


Clarifying Concepts

The physical schema contains all the necessary pieces to define the database: tables, columns, types, indexes, constraints, etc. This is what we are really defining. Every object added in the graphical model also shows up in the physical schema. It is, in fact, a visual way to define our schema.

We can have several schemas for the same database model in the same way we can have several databases in a MySQL server. Each schema will be a MySQL database. For example, in the next screen, we have two schema tabs:

If we generate the SQL script, we will have two separate CREATE DATABASE statements - actually we will have CREATE SCHEMA which is just a synonym.

CREATE SCHEMA IF NOT EXISTS `schema1`;
CREATE SCHEMA IF NOT EXISTS `schema2`;

"EER stands for Extended (or Enhanced) Entity-Relationship. EER diagrams are just a way to model the data and the relationships between data using standard symbols"

They will be listed as databases within the MySQL server host when using SHOW DATABASES.

Now, what is an EER Diagram?. EER stands for Extended (or Enhanced) Entity-Relationship>. EER diagrams are just a way to model the data and the relationships between data using standard symbols. EER models can be complex, but MySQL Workbench uses only a subset of all possible graphical elements, because the purpose of this diagram (in this tool) is to have every element mapped to the physical schema.

We can use an EER diagram to define the whole database, or just small parts. For example, we can have a schema with five tables defined, and then create a new diagram to define two more tables using the visual editor. The diagram will contain only two tables, but those two tables will also be included in the schema, together with the previous five.


Creating our Tables

Back to our initial example; we have to rename the default schema by double clicking the name. At this point, we have two possibilities: we can start adding tables to our physical schema using the add table icon, or we can start an EER Diagram and add all the tables there.

I prefer to add a new diagram from the beginning and create my schema visually; however, in order to show how to do it with both methods, we are going to create the first two tables in the schema tab, and then continue with the EER Diagram.

When you click the Add Table icon, the table editor opens as a tab below:

Using the table editor, we change the table name and switch to the columns tab (in the tabs below the editor) to enter our columns. We can choose the data type (there is a drop-down list with all the MySQL data types), assign default value, if needed, and we have seven checkboxes to mark any of the following properties:

  • PK - Primary key
  • NN - Not null
  • UQ - Unique
  • BIN - Binary
  • UN - Unsigned
  • ZF - Zero fill
  • AI - Autoincrement

Go Visual

This is one way to add our tables, though we can also create them using the diagrams. If we click the Add Diagram icon now, we will begin a new, empty diagram, and that's not what we want. We want the two tables that we just created to be in the diagram.

If we go to the menu, select Model/Create Diagram from Catalog Objects, now we have our diagram, and are ready to continue.

Select the table icon on the left; the pointer changes to a hand with a little table. Next, click anywhere in the canvas to create a new table.

Now you just have to double click the table, and the editor tab appears to edit the name, columns, types, etc.- the same way as we did before.

After entering the column details for the new tables, we'll be ready to start drawing the relationships.


Drawing Relationships

In the vertical tool bar on the left, we have six tools available to create relationships.

Don't worry about the last one, we'll explain it later. For the 1:1 and 1:n relationships, we have two different types of symbols: identifying and non identifying. What does that mean?

A relationship is considered identifying when one table is entirely dependent on the other to exist.

A relationship is considered identifying when one table is entirely dependent on the other to exist. A row in that table depends on a row in the other table. A common example is to have a separate table to store phones for users. It may be necessary to have it in another table, because there can be several phones for one user, but each row in that table is entirely dependent on the user - it belongs to the user.

You should be aware that relationships have some implications. If we want to create the physical tables in MySQL, relationships must be mapped in some way. There are a few rules to map relationships into tables:

  • 1:1 relationships. Primary key for one of the tables is included as foreign key in the other table.
  • 1:n relationships. Primary key of the table in the '1' side is added as foreign key in the table in the 'n' side.
  • n:m relationships. A new table (join table) is created. The primary key is composed of the primary keys from the two original tables.

Identifying relationships are typically used for the join tables created from a many-to-many relationship. These new tables are entirely dependent on the two original tables.

Also, in the case of 1:1 and 1:n identifying relationships, the foreign key introduced will be part of the primary key for that table, forming a composite primary key.

The good news is that MySQL Workbench knows these rules better than most of us. We just draw our lines, and the foreign keys or join tables will be automatically be created. We can also choose to do it manually, as we'll see shortly.

To draw a relationship, click the icon, and then click the two tables to relate. For one-to-many relationships, click on the "many" side table first, and then on the "one" side table. Let's see how to do it for the n:m teachers-subjects relationship, and for the 1:n teachers-classes.

The default name assigned for the foreign keys, and for the join tables can be changed globally in Edit/Preferences/Model Tab, or only for the present project in Model/Model Options.

If we don't want tables and foreign keys to be generated in this way, we can use the mysterious "sixth symbol."

The "sixth symbol" creates a relationship using existing columns, meaning that you have already included the necessary foreign keys in your tables and created the necessary join tables (n:m mapping tables). Since we've already created these Join tables, we don't need n:m relationships; only 1:n is available.

When we have all our relationships defined, our diagram should looks like so:

Be aware that we have been using the default MySQL Workbench notation for the diagrams, but you can change that in Model/Object Notation and Model/Relationship Notation. This is an example of our model in Classic notation:

At this point, our model is ready, and we can generate the SQL to create the MySQL database.


Generating SQL

Select File/Export/Forward Engineer SQL CREATE Script. We are only three wizard screens away from generating our file!


We even have the option to review and edit the generated SQL before saving it:

And that's it. Clicking finish, the SQL script will be generated and saved. Now, we can use it in any way we wish. We can load it using the command-line mysql client:

mysql> SOURCE scriptName.sql

Or, we can use MySQL Workbench to finish the work, connecting to our MySQL server and running the script.


Connecting to a MySQL Server

Select Database/Manage Connections from the menu, and click NEW.

If you don't want to set the password here, you'll be prompted for it when needed. Click "Test Connection" to check if your parameters are correct, and then click close.

Now, to load the script, we'll use the SQL editor. In the main menu select Database/Query Database; a window prompts you to select a connection, and then the SQL editor tab opens.

Now click the lightning icon to execute the SQL script, and your database will be generated!

We could also have generated the MySQL database directly from the model, without referencing the actual file, using Database/Forward Engineer from the menu; however, I find it useful to generate the script and then use it how I wish.


Conclusion

MySQL Workbench is an impressive tool. We have only seen a few basic possibilities in the data modeling part, and only peeked at the SQL editor in the second half of this tutorial. We learned how to create a database visually and draw diagrams that can be kept as documentation. You can export the diagrams as a PNG, SVg, PDF or PostScript file. Thanks for reading, and let me know what you think!

Related Posts
  • Code
    PHP
    SOLID: Part 4 - The Dependency Inversion Principle4 dip retina
    The Single Responsibility (SRP), Open/Closed (OCP), Liskov Substitution, Interface Segregation, and Dependency Inversion. Five agile principles that should guide you every time you write code.Read More…
  • Code
    Databases
    Mapping Relational Databases and SQL to MongoDBMongodb retina preview
    NoSQL databases have emerged tremendously in the last few years owing to their less constrained structure, scalable schema design, and faster access compared to traditional relational databases (RDBMS/SQL). MongoDB is an open source document-oriented NoSQL database which stores data in the form of JSON-like objects. It has emerged as one of the leading databases due to its dynamic schema, high scalability, optimal query performance, faster indexing and an active user community.Read More…
  • Code
    PHP
    Building a Customer Management App Using AngularJS and LaravelLaravel 4 auth retina preview
    When creating a single-page app we should use some kind of framework to do some of the job for us, so we can focus on the actual functionality. AngularJS fits here perfectly, because features like dynamic dependency injection and bi-directional data binding are just great. Sometimes we also require some kind of server. If you've chosen PHP then Laravel may be your best option, as it's easy to work with and pretty powerful.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Features: Saving DataApplication foundation 400
    When it comes to the web, almost any application that runs within your browser is backed by a data store of some type. Usually, these data stores are in the form of a type of database. Until recently, most applications were built on top of some type of SQL database, but with the rise of document-based databases such as CouchDB, other applications have begun to use other backends as well.Read More…
  • Game Development
    Implementation
    How to Code a Self-Hosted PHP/SQL Leaderboard For Your GamePhp sql self hosted leaderboard 400px
    In this article, we're going to create our first MySQL leaderboard to host on a website or web server using simple PHP and some SQL. We'll then make a simple Unity example in C# using GUIText objects to add new scores to our leaderboard, display the top ten scores and display a user's score and rank.Read More…
  • Code
    Plugins
    Migrating Your WordPress Database: A Database PrimerMigrating your wordpress database 400
    When it comes to working with WordPress-based projects, arguably one of the most frustrating or tedious aspects of deployment is actually getting the databases across your environments in-sync with one another. Sure, there's something to be said for using test data in development, user data in staging, and actual data in production, but there's no such thing as a silver bullet, right? That means that sometimes test data is going to work; other times, it won't. For example, let's say that you inherit a project for which you have to pull down a database and then begin working with existing data. Or let's say that you have to migrate an entire site or application from one server to another. In cases like that, test data doesn't help a whole lot. Instead, you need a tool for it. And sure, the WordPress Importer is a fair tool for basic migrations, and running SQL exports and imports is okay if you're comfortable with database front-ends and working with SQL itself. But what about those who are somewhere in between?Read More…