Master Database Design with Navicat: A Step‑by‑Step Guide

Navicat

Navicat is a fast, reliable database management tool designed to simplify database administration and reduce system management costs, catering to DBAs, developers, and small‑to‑medium enterprises with an intuitive graphical interface.

Note: This tutorial uses Navicat 12; download link: https://www.navicat.com.cn/download/navicat-premium

Database Design

We will use Navicat to design the database for the mall project's permission management module.

Open Model

First open Navicat's database design feature via the

Model

button on the toolbar.

New Table

Click the Table button on the toolbar to create a new table.

After creation, double‑click Design Table to add fields; we created a table ums_admin .

Create Foreign Key Relationships

If tables lack foreign keys, relationships become unclear as the schema grows.

Create two tables ums_role and ums_admin_role_relation to demonstrate a many‑to‑many relationship, then use the Foreign Key button.

Click the Foreign Key button and select the field admin_id ; a small connector appears.

Double‑click the connector to edit: reference table ums_admin , reference field id .

After editing, the foreign‑key line is displayed.

Build the remaining mall permission tables and practice.

If the layout looks messy, use the Auto Layout feature.

Export SQL

When exporting, disable foreign‑key generation because the database may rely on logical relations.

The Export SQL function is located under the Tools menu.

In the Advanced options, deselect foreign‑key generation, then confirm to export the SQL statements.

Reverse Engineering

Some users cannot open PowerDesigner files; Navicat can reverse‑engineer a database design.

Prepare an SQL file with foreign keys (e.g., https://github.com/macrozheng/mall-learning/blob/master/document/navicat/mall-ref.sql) and import it into the pd-test database.

Use Navicat’s Reverse Engineering tool (under Tools) to generate a design diagram.

Select the database pd-test to import.

After import, the complete relational diagram appears; you can adjust table positions as desired.

Conclusion

Overall, Navicat’s database design feature is simple, elegant, and lighter than PowerDesigner, making it a practical choice for efficient schema modeling.