Visual MySQL Database Design in MySQL Workbench

Exclusive offer: get 50% off this eBook here
Creating your MySQL Database: Practical Design Tips and Techniques

Creating your MySQL Database: Practical Design Tips and Techniques — Save 50%

A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.

£7.99    £4.00
by Djoni Darmawikarta | June 2008 | MySQL PHP

MySQL Workbench is a visual database design tool recently released by MySQL AB. The tool is specifically for designing MySQL database.

MySQL Workbench has many functions and features; this article by Djoni Darmawikarta shows some of them by way of an example. We’ll build a physical data model for an order system where an order can be a sale order or a purchase order, and then, forward-engineer our model into an MySQL database.

MySQL Workbench is a visual database design tool recently released by MySQL AB. The tool is specifically for designing MySQL database.

What you build in MySQL Workbench is called physical data model. A physical data model is a data model for a specific RDBMS product; the model in this article will have some MySQL unique specifications. We can generate (forward-engineer) the database objects from its physical model, which in addition to tables and their columns, can also include other objects such as view.

MySQL Workbench has many functions and features; this article by Djoni Darmawikarta shows some of them by way of an example. We’ll build a physical data model for an order system where an order can be a sale order or a purchase order; and then, forward-engineer our model into an MySQL database.

The physical model of our example in EER diagram will look like in the following MySQL Workbench screenshot.

Visual MySQL Database Design in MySQL Workbench

Creating ORDER Schema

Let’s first create a schema where we want to store our order physical model. Click the + button (circled in red).

Visual MySQL Database Design in MySQL Workbench

Change the new schema’s default name to ORDER. Notice that when you’re typing in the schema name, its tab name on the Physical Schemata also changes accordingly—a nice feature.

The order schema is added to the Catalog (I circled the order schema and its objects in red).

Visual MySQL Database Design in MySQL Workbench

Close the schema window. Confirm to rename the schema when prompted.

Visual MySQL Database Design in MySQL Workbench

Creating Order Tables

We’ll now create three tables that model the order: ORDER table and its two subtype tables: SALES_ORDER and PURCHASE_ORDER, in the ORDER schema. First of all, make sure you select the ORDER schema tab, so that the tables we’ll create will be in this schema.

We’ll create our tables as EER diagram (EER = Enhanced Entity Relationship). So, double-click the Add Diagram button.

Visual MySQL Database Design in MySQL Workbench

Select (click) the Table icon, and then move your mouse onto the EER Diagram canvas and click on the location you want to place the first table.

Visual MySQL Database Design in MySQL Workbench

Visual MySQL Database Design in MySQL Workbench

Repeat for the other two tables. You can move around the tables by dragging and dropping.

Visual MySQL Database Design in MySQL Workbench

Next, we’ll work on table1, which we’ll do so using the Workbench’s table editor. We start the table editor by right-clicking the table1 and selecting Edit Table.

Visual MySQL Database Design in MySQL Workbench

Next, we’ll work on table1, which we’ll do so using the Workbench’s table editor. We start the table editor by right-clicking the table1 and selecting Edit Table.

Rename the table by typing in ORDER over table1.

Visual MySQL Database Design in MySQL Workbench

We’ll next add its columns, so select the Columns tab. Replace idORDER column name with ORDER_NO.

Visual MySQL Database Design in MySQL Workbench

Select INT as the data type from the drop-down list.

Visual MySQL Database Design in MySQL Workbench

We’d like this ORDER_NO column to be valued incrementally by MySQL database, so we specify it as AI column (Auto Increment).

AI is a specific feature of MySQL database.

Visual MySQL Database Design in MySQL Workbench

You can also specify other physical attributes of the table, such as its Collation; as well as other advanced options, such as its trigger and partioning (the Trigger and Partioning tabs).

Visual MySQL Database Design in MySQL Workbench

Notice that on the diagram our table1 has changed to ORDER, and it has its first column, ORDER_NO. In the Catalog you can also see the three tables.

The black dots on the right of the tables indicate that they’ve been included in an diagram.

Visual MySQL Database Design in MySQL Workbench

 


Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: £7.99
Book Price: £12.99
See more
Select your format and quantity:

If you expand the ORDER folder, you’ll see the ORDER_NO column. As we define the column as a primary key, it has a key icon on its left.

Visual MySQL Database Design in MySQL Workbench

Back to the table editor, add the other columns: ORDER_DATE and ORDER_TYPE. The ORDER_TYPE can have one of two values: S for sales order, P for purchase order. As sales order is more common, we’d like to specify it as the column’s default value.

You add the next column by double-clicking the white space below the last column.

Visual MySQL Database Design in MySQL Workbench

In the same way, create the SALES_ORDER table and its columns.

Visual MySQL Database Design in MySQL Workbench

Lastly, create the PURCHASE_ORDER table and its columns.

Visual MySQL Database Design in MySQL Workbench

Create Relationships

We have now created all three tables and their columns. We haven’t done yet with our model; we still need to create the subtype relationships of our tables.

The SALES_ORDER is a subtype of ORDER, implying their relationship is 1:1 with the SALES_ORDER as the child and ORDER the parent, and also the ORDER’s key migrated to the SALES_ORDER. So, select (click) the 1:1 identifying relationship icon, and click it on the SALES_ORDER table and then ORDER table.

Visual MySQL Database Design in MySQL Workbench

Notice that the icon changes to a hand with the 1:1 relationship when you click it to the tables.

Visual MySQL Database Design in MySQL Workbench

Visual MySQL Database Design in MySQL Workbench

The 1:1 relationship is set; the ORDER_NO primary key is migrated and becomes the primary key of the SALES_ORDER table.

Visual MySQL Database Design in MySQL Workbench

Next, create the PURCHASE_ORDER to ORDER relationship, which is also 1:1 identifying relationship.

Visual MySQL Database Design in MySQL Workbench

We have now completed designing our tables and their relationships; let’s save our model as ORDER.mwb.

Visual MySQL Database Design in MySQL Workbench

Visual MySQL Database Design in MySQL Workbench

Generate DDL and Database

The final step of our data modeling in this article is generating the model into MySQL database. We’ll first generate the DDL (SQL CREATE script), and then execute the script.

From the File | Export menu, select Forward Engineer SQL CREATE Script.

Visual MySQL Database Design in MySQL Workbench

Visual MySQL Database Design in MySQL Workbench

Visual MySQL Database Design in MySQL Workbench

Lastly, execute the saved SQL CREATE script. We execute the script outside of MySQL Workbench; for example, we can execute it in a MySQL command console.

Visual MySQL Database Design in MySQL Workbench

Visual MySQL Database Design in MySQL Workbench

You can also confirm that the tables have been created.

Visual MySQL Database Design in MySQL Workbench

Summary

This article shows you how to build a MySQL physical data model visually in MySQL Workbench, and generate the model into its MySQL database.

Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: £7.99
Book Price: £12.99
See more
Select your format and quantity:

About the Author :


Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.

Books From Packt

Magento 1.3: PHP Developer's Guide
Magento 1.3: PHP Developer's Guide

Joomla! 1.5: Beginner's Guide
Joomla! 1.5: Beginner's Guide

PHP 5 E-commerce Development
PHP 5 E-commerce Development

Spring Python 1.1
Spring Python 1.1

Drupal 6 Search Engine Optimization
Drupal 6 Search Engine Optimization

NHibernate 2 Beginner's Guide
NHibernate 2 Beginner's Guide

Magento 1.3 Sales Tactics Cookbook
Magento 1.3 Sales Tactics Cookbook

Drupal 5 Themes
Drupal 5 Themes

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software