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.

Creating ORDER Schema

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

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).

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

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.

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.

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

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.

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.

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

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

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.

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).

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.

A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily. How best to collect, name, group, and structure your data Design your data with future growth in mind Practical examples from initial ideas to final designs http://www.PacktPub.com/creating-mysql-tips/book

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.

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.

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

Lastly, create the PURCHASE_ORDER table and its columns.

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.

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

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

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

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

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.

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.

You can also confirm that the tables have been created.

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.

Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 2.11 Effectively administer your MySQL databases with phpMyAdmin Manage users and privileges with MySQL Server Administration tools Get to grips with the hidden features and capabilities of phpMyAdmin http://www.PacktPub.com/phpmyadmin-3rd-edition/book

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.