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.

 

---