Simplifying MySQL Database Design using a Graphical Data Modeling Tool

Monday May 9th 2016 by Rob Gravelle
Share:

As in all aspects of database development and management, specialized software has emerged to make the process of proper database design much easier to accomplish. In today’s tutorial, Rob Gravelle shows you how to design, implement, maintain, and synchronize MySQL database schemas using the Navicat Data Modeler.

A proper database design is essential to achieving your goals in working with a database, whether they prioritize rapid information retrieval, efficient storage, or some combination of both.  Therefore, investing the time to apply the principles of good design should be a high priority.  Once completed, your database will better meet your needs and can easily accommodate future changes.

As in all aspects of database development and management, specialized software has emerged to make the process much easier to accomplish.  In today’s tutorial, we’ll learn how to design, implement, maintain, and synchronize MySQL database schemas using just such an application - the Navicat Data Modeler.  

What is Data Modeling?

Data modeling involves the formalization and documentation of data entities used within a business or other context as well as the identification of the relationships between these data entities.

There are three levels of data modeling:

  1. Conceptual: A conceptual data model identifies the highest-level relationships between the different entities.
  2. Logical: A logical data model describes the data in as much detail as possible, but without regard to how they will be physical implemented in the database.
  3. Physical: Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column names, column data types, column constraints, primary key(s), foreign keys, and relationships between tables.

How a GUI Application Simplifies Model Generation 

GUI Applications like the Navicat Data Modeler automate the writing of Data definition language (DDL) statements.  Rather than write them yourself, you create, modify, and design your models within object designers, which generate the DDL statements behind the scenes. Navicat Data Modeler supports three standard model notations: Crow’s Foot, IDEF1x and UML.

In addition to simplifying the creation of data models, GUI tools can link models to tables and/or views so that changes to a model may be deployed to database structures directly.  All structural elements from entity relationships, table/view attributes and columns may thusly be created and managed from within the model.  Likewise, using a process known as reverse engineering, new Entity Relationship diagrams may be created from existing database structures.

Navicat Data Modeler offers one additional feature: being fully integrated with Navicat Cloud, it enables you to synchronize your model files and virtual groups to the cloud in real-time.

Getting Started

The trial version of the Navicat Data Modeler may be downloaded from the company’s website for the Linux, Windows, and Mac operating systems.   The 14-day trial version of the software is identical to the full Enterprise Edition so you can get the full impression of all its features.  Moreover, registering with PremiumSoft via the “location 3” links gives you free email support during the trial period.

For the purposes of this tutorial, instructions and screenshots will pertain to the Windows edition.  On your O/S, procedures and appearances may differ slightly.  Please refer to the documentation for more specific information.

Upon launching Navicat Data Modeler, a Welcome window appears. It contains several commands, including one to create a new model, open an existing model file, and links to recent models.  There is also a Sign In form for Navicat Cloud. Upon successful logon to Navicat Cloud, the Welcome window splits into two parts: your local Computer and Navicat Cloud.  You can then access and save models to either the local computer or Navicat Cloud*:

Navicat Data Modeler
Navicat Data Modeler

* Navicat Cloud is beyond the scope of this article. To learn more about it, see the MySQL Collaboration in the Cloud article.

Creating a New Conceptual Model

A model is a conceptual, logical, or physical representation of a schema.  It may have many diagrams associated with it, which each depict the entire schema or a part thereof.  There are two ways to create a new model in Navicat Modeler: you can create it from scratch or generate it from an existing database.  We’ll start with the first option.

The model that we will create depicts a Star Schema, whereby you have a central data entity, surrounded by several peripheral ones.  The Star Shema gets its name from the resemblance of the diagram to a star.  Our model will contain a sales entity with three peripheral ones for dates, stores, and products. 

  • To create a conceptual model, select File -> New Model from the main menu. In the New Model window, choose Conceptual as Model Type.

New Model
New Model

That will open a new Diagram Editor window.

Before we continue any further, let’s take a moment to familiarize ourselves with the Diagram Editor’s interface:

Diagram Editor Interface
Diagram Editor Interface

Diagram Editor Interface Components:

  1. Main Toolbar
  2. Logged-in Cloud user
  3. Diagram elements
  4. Action History
  5. Diagram design grid
  6. Element properties
  7. Visible diagram area and Zoom level slider

Renaming the Diagram

Once the new diagram is created, you will likely want to rename it to something more informative. 

  • To do that, double-click the title in the Element Palette or right-click it and select Rename from the popup menu:
    Rename the Diagram
    Rename the Diagram
  • Let’s rename it to “Sales Star Diagram Conceptual”.

Adding Elements to the Diagram

There are a couple of ways to add an element to the diagram.

The first is to click the Entity command at the top of the screen

Alternatively, you can right-click anywhere in the editor and select the element from the popup menu.  Elements include Entities, Labels, Notes, Images, Layers, and Shapes:

Adding Elements to the Diagram
Adding Elements to the Diagram

  • Select the Entity menu item form the popup menu.

Once an element is placed on the editor grid, its name, if applicable, immediately becomes editable:

Element’s Name Becomes Editable
Element’s Name Becomes Editable

Adding Relationships between Entities

Notice that, in the New Element popup menu above, there is no relationship item.  Those must be added using the Relation command from the Main Toolbar.

  • After you click the button, the mouse cursor turns into a chain link. As you hover the cursor over an entity, the entity becomes highlighted.  Click the entity to select it:

    Adding Relationships between Entities

    Adding Relationships between Entities

  • Then, with the left mouse button depressed, drag the cursor over the second entity, before releasing the button:

    Drag the Cursor over the Second Entity
    Drag the Cursor over the Second Entity

At that point, the relation line will appear.

But that is just the first step; from there, you can:

  • Drag the connectors to any point around the entity.
  • Modify the line’s properties via the Object properties tab, including its color and cardinality.
  • Change the routing from oblique style to rectilinear via the Add Vertex popup menu command.

Setting Cardinality

In a Star Schema, the peripheral entities typically have a one-to-many relationship with the central entity.  With respect to our sales model, a peripheral entity may have “One and Only One” instance to the sales entity’s “One of Many” references to the peripheral entity.

Cardinality between entities may either be set on the Object properties tab or via the popup menu. The latter method offers the advantage that it shows each entity’s name. Also notice that commands for adding and deleting Vertices are also found there:

Setting Cardinality
Setting Cardinality

Here is a completed relationship, including cardinality and one Vertex:

Completed Relationship
Completed Relationship

  • Follow the same procedure as above to add two more Entities to the diagram.
  • Add a Relation line between each peripheral Entity and the principal (center) one.

Here is what the finished diagram should look like:

Finished Diagram
Finished Diagram

Converting a Model into another Type

All the work that you put into the Conceptual model need not go to waste.  We can utilize it as the basis for the Logical model. 

  • Select File > Model Conversion... from the main menu to open the Convert to New Model dialog.
  • Choose Logical as the Model Type and click OK:

Convert to New Model
Convert to New Model

Note that the Target Database dropdowns are disabled because a Logical model does not contain database-specific information.

Navicat Modeler will create a new Logical Model of the Sales Star Schema.  Entities and their relationships will be carried over, so that we can dive right into attribute (a.k.a. field) creation.

Here are the attributes for each of the four entities:

t_sales:

Name

Type

(Char) Length

Decimals*

Not Null

Default Value**

id (PK)

INTEGER

0

0

Yes

N/A

date_id

INTEGER

0

0

Yes

N/A

store_id

INTEGER

0

0

Yes

N/A

product_id

INTEGER

0

0

Yes

N/A

units_sold

INTEGER

0

0

No

NULL

*Decimals only apply to numeric fields.
** Can be either NULL or an EMPTY STRING.

t_dates:

Name

Type

Length

Decimals

Not Null

Default Value

id (PK)

INTEGER

0

0

Yes

N/A

day

CHAR

2

0

No

NULL

month

CHAR

2

0

No

NULL

year

CHAR

4

0

No

NULL

quarter

NUMERIC

1

0

No

NULL

t_stores:

Name

Type

Length

Decimals

Not Null

Default Value

id (PK)

INTEGER

0

0

Yes

N/A

store_num

INTEGER

0

0

Yes

N/A

name

CHAR VARYING

255

0

Yes

N/A

t_products:

Name

Type

Length

Decimals

Not Null

Default Value

id (PK)

INTEGER

0

0

Yes

N/A

prod_code

CHAR VARYING

25

0

Yes

N/A

name

CHAR VARYING

255

0

Yes

N/A

prod_category

CHAR VARYING

50

0

Yes

N/A

The Converted Sales Star Schema Model
The Converted Sales Star Schema Model

  • The easiest way to define an entity’s attributes is to right-click it and choose Design Entity from the popup menu.
    That brings up the Entity Design dialog.  It provides tabs for managing attributes (fields), indexes, relations, uniques, and comments:

t_dates - Entity
t_dates - Entity

 

The Completed Sales Star Schema Logical Model
The Completed Sales Star Schema Logical Model

Creating a Physical Model

The Model Conversion command can also be employed to create a Physical Model from the Logical one.

  • On the Convert to New Model dialog, select MySQL 5.6 as the target database.

The Completed Sales Star Schema Physical Model
The Completed Sales Star Schema Physical Model

If you right-click a table and choose Design Table... from the popup menu, you’ll notice that the Design Table dialog contains information that pertains to the specific database type and version that you selected on the Convert to New Model dialog.

The Design Table Dialog
The Design Table Dialog

Creating a Model from an Existing Database

Creating new diagrams from existing database structures is a process known as Reverse Engineering. Navicat Data Modeler supports the importing of MySQL, MariaDB, PostgreSQL, Oracle, SQLite, SQL Server tables/views, as well as from ODBC data sources. If the model is logical or conceptual, all views will convert to entities after the import process.

This functionality is included as the Import from Database feature. To start the process, select Tools -> Import from Database from the main menu.

On the Import from Database dialog, you may choose to connect to a data source mentioned above via a new connection or by importing your connections from one of the Navicat Database Management tools:

Import from Database
Import from Database

Once a connection is established, you can pick and choose the tables and views that you want to include in your model:

Import from Database
Import from Database

Here are the three models created from the public Sakila database:

Conceptual Model
Conceptual Model (click for larger image)

Logical Model
Logical Model (click for larger image)

Logical Model – Close Up
Logical Model – Close Up (click for larger image)

Physical Model
Physical Model (click for larger image)

Physical Model – Close Up
Physical Model – Close Up (cclick for larger image)

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:

Export SQL
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:

Export SQL – Advanced Tab
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):

Synchronize to Database
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
--------------------------------------------------

Tips & Tricks

Auto Layout

The Auto Layout button - found on the main toolbar - automatically arranges objects on the canvas.  This is often faster and easier than trying to arrange every diagram component yourself.

Here is a Conceptual Diagram of a Travel Bookings before and after executing the Auto Layout command:

Before
Before

After
After

For more control over the diagram’s appearance, Auto Layout format settings may be set from the Diagram -> Auto Layout with... command on the main menu.  Options include:

  • Auto Diagram Dimension
    Choose the suitable diagram dimension automatically.
  • Auto Size Tables To Fit
    Resize the table to fit its content automatically.
  • Quality
    The quality of the auto layout output.
  • Object Distance
    The distance between the objects in the diagram.

Undoing Operations

One of the many advantages of using a GUI Modelling tool is the ability to easily undo one or many operations.  In Navicat Data Modeler, actions are tracked and displayed in the Action History list.  Clicking any item in the list instantly reverts the diagram state to that snapshot.

The Action History List
The Action History List

Conclusion

Considering the complexities involved in keeping models and database structures synchronized it only makes sense to use a specialized tool to facilitate the propagation of changes from one to the other. The tool that we employed here today - Navicat Data Modeler - is available for the Windows, Mac OSX, and Linux platforms from the Navicat store   An individual commercial license is $249.00 USD while a Non-Commercial Edition license may be purchased for $199.00 USD. 

See all articles by Rob Gravelle

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved