Database/Data Model Round-Trip Engineering with MySQL

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

Power*Architect—from SQL Power—is a free software data modeling tool, which you can download from its website www.sqlpower.ca and use it under GPLv3 license.

Reverse Engineering

To reverse engineer is to create the data model of an existing database.

To reverse engineer an existing database in Power*Architect, we need to connect to the database. Figure 1 shows the Power*Architect's connection window where we define (create) our connection to the MySQL sales database that we'd like to reengineer.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 1: Creating a database connection

By adding the conn_packt connection, the sales database objects are now available in Power*Architect.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 2: Adding a database connection

By expanding the sales database, you can see all the objects that you need to create its data model.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 3: Database objects

You create the ER diagram of the sales data model by dragging the sales object into the canvas (called playpen in Power*Architect)

Note that the objects in the model (those in the diagram) are now in the PlayPen Database.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 4: Database objects in the PlayPen

Now that you have created the data model, you might want to save it.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 5: Saving the data model (project)

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 6: Saving sales.architect data model (project)

You have completed the sales database reverse-engineering.

Updating the Data Model

Let's now add two new tables (hardware and software) and relate them to the product table. You add a table by clicking the New Table tool and dropping your cursor on the white space of the canvas.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 7: New Table tool

Type in the name of the table, and then click OK.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 8: Adding hardware table

We now add a column to the hardware table by right-clicking the table and selecting New Column.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 9: New Column menu selection

Type in the name of the column (model), select VARCHAR data type (and its length), then click OK.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 10: The model column

After adding the two tables and their columns, our ER diagram will look like in Figure 11.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 11: The hardware and software tables

Our last update is relating the hardware and software tables to the product table.

Select the New Identifying Relationship tool; click it to the product and then the software.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 12: New Identifying Relationship tool

The software table is now related to the product table. Note that the product's primary key is migrated to the software table as a primary key.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 13: software and product tables are related

Do similarly with the hardware table. When you're done, the ER diagram will look like in Figure 14.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 14: Model changes completed (hardware and software tables)

Save the model.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 15: Saving the updated model

Comparing Data Models

To implement the changes we made back to the database, we need to compare the update data model to the existing database.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 16: Compare menu icon

Our old "model" is the existing database; the newer is the model (project) we're working on, so click the Swap button to exchange the default (Figure 17).

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 17: Swapping the older and newer models

Select the conn_packt connection (Figure 18), sales database (Figure 19) and SQL for MySQL (Figure 20).

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 18: Selecting conn_pact connection

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 19: Selecting sales database

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 20: Selecting SQL for MySQL

When you click Start, the DDL to implement the changes are generated and shown as in Figure 21.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 21: Generating DDL

To finish off our data models comparison click Save, which saves the DDL in an SQL script.

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 22: Saving DDL

Forward Engineering

You can implement the changes by running the SQL script you just saved. Another easier way is to just click the Execute button on the Compare DM window. (Figure 21 above)

When the execution gets completed, you'll get a successful message (Figure 23).

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 23: Forward-reengineering is completed

The sales database is now updated with the changes: the software and hardware tables, and their relationship to the product table (Figure 24).

Creating your MySQL Database: Practical Design Tips and Techniques

Figure 24: sales database updated

We have now completed our round-trip engineering, which started by reverse engineering an existing sales database, updated the resulting data model, compared the old and new data models to get the updating DDL, and finally forward engineering to apply the update back to the sales database.

Summary

This article shows the no-nonsense functions of the SQL Power's Power*Architect for round-trip engineering database/data model. In addition to MySQL, the tool supports other databases, namely PostgreSQL, HSQLDB, SQL Server, Oracle, and IBM DB2. The tool has other data-related functions, such as data profiling, integration with Kettle (an open source ETL tool), and OLAP data modeling.

Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free