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:
- Conceptual: A conceptual data model identifies the highest-level relationships between the different entities.
- 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.
- 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 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
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 Components:
- Main Toolbar
- Logged-in Cloud user
- Diagram elements
- Action History
- Diagram design grid
- Element properties
- 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
- 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
- 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
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
- 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
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
Here is a completed relationship, including cardinality and one Vertex:
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
