Wednesday, 3 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 4

Creating Database Structures from a Model

Once the Physical Model has been finalized, it acts as a template for the real database structure.  Building the database schema from a model typically involves consulting the Physical Model for each step of database construction, much like blueprints in the construction of buildings. Some of the downsides to this approach is that it is time consuming and prone to errors.

Navicat Data Modeler offers two forward engineering tools for you to produce a script file or even generate the database schema directly from a physical model.

Exporting to an SQL File

Navicat Data Modeler’s Export SQL tool allows you to generate an SQL file with customized settings.  To use it:
  • Select Tools > Export SQL... from the main menu of the Diagram Editor to bring up the Export SQL options dialog.
The Export SQL dialog contains two Tabs: General and Advanced.  The General tab contains a File Browsing control for setting the path of the SQL file as well as an Object Tree containing all of the tables and views represented by the model:

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 4
Export SQL

The Advanced Tab lets you set additional options based on the diagram database type.  Options include the server version for the SQL file, the default schema name, and whether or not to include the schema name in the file:

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 4
Export SQL – Advanced Tab

Synchronizing with a Database

The Synchronize to Database feature does more than simply create a new schema from your physical models, it allows you to compare a model with an existing database or schema, states the differences between their structures, and offers suggestions for synchronizing model objects to the target database. To start the Synchronize to Database wizard, select Tools -> Synchronize to Database from the main menu.

There are two synchronizing strategies to choose from:
  • Synch with selected schemas compares all objects in your selected model schemas with those of the target database.  Objects that do not exist in both will be dropped from the target database schema.
  • Sync with selected objects limits comparison to selected model objects and existing objects in the target database.  Hence, no Drop statements are executed. 
You may choose one or more schemas or objects in a model to compare to the target schemas or objects. If the model represents an existing schema, you can select it from a list. For new schemas, you may enter the target schema name to create from the source model.

It is possible to select which schema objects to include in comparisons as well as what execution statements are produced as a result.  Comparable objects include tables, views, primary keys, foreign keys, indexes, triggers, character sets, and auto-increment values, among others.  Executable SQL statements include CREATE, ALTER, and DROP.

Differences between the source model and target connection are displayed side-by-side on the Results screen for review.  Blue font indicates differing items (altered) while objects written in Red font exist in one of the compared schemas only (new):

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 4
Synchronize to Database

All Queries required to make the target schema match the source model are displayed at the bottom of the screen along with a checkbox. Selecting at least one checkbox enables the Run Query button. 

Clicking the Run Query button synchronizes the selected target objects with those of the source Model. As each SQL statement is executed, the results are displayed in a textarea: 

[Msg] Starting Synchronization 
[Msg] Target Server : sakila
[Msg] Executing - set FOREIGN_KEY_CHECKS=1
[Msg] Completed
[Msg] Synchronization Completed