MySQL database design

Quick-start guide for MySQL DB database design

1. New project
2. Tables and columns
3. JSON datatypes
4. Relations and foreign keys
5. Code generation

1. New project for MySQL

To create a new project, click the New icon on the main toolbar and select the MySQL project type.

2. Adding tables and columns to the project and diagram

New database tables can be added into projects by clicking Table on the toolbar and then by clicking the main diagram area.

A new graphical object will appear on the diagram.

You can edit MySQL table properties via Right Side panel:

Alternatively, you can double click the graphics or right-click the item and choose Edit.

Table properties

Database table settings can be specified in the section Details.

Columns

In section Columns, you can add new table columns. Specify new column name, datatype, and parameter and click Add.
PK indicates the Primary key.
NN represents the Not Null field.

Column specifics

To access the area where MySQL column specifics can be set, click the arrow left to the column name.

3. JSON datatypes

MySQL contains support for the JSON datatype.  It is an ideal format for storing data defined in hierarchical structures. Many relational database systems allow you to store JSON data, however, there are not many database tools that offer visualization of the structures stored in JSON. Usually, database modeling tools show columns as items in tables, but without nested structures.

Moon Modeler combines entity-relationship diagram features with NoSQL features.

If you wish to hide the JSON structure, use the JSON datatype.
And if you prefer full visualization, add a new JSON to your diagram, defined JSON structure, and then use the pre-defined structure as a data type for your database column.

To add a new JSON to your database model, click JSON on the toolbar and then click the diagram area.

Then you can define details in the right-side panel or in a modal dialog. Datatypes change from MySQL to JSON basic types.

Using JSON in table columns

Edit table and select JSON name from the Datatype selection box.

4. Relations and foreign keys

Relations can be drawn between two database tables. To create a new relation, click Relation on the toolbar and then click parent and then child table.
When a new relation is created, a foreign key appears in the child table automatically.

In the details section of relation, you can specify referential integrity, used key (primary key or alternate key), and cardinality.

5. Code generation

To preview the SQL script for your MySQL database design, click the Script tab on table detail.

To save scripts to files, click the Script icon on the toolbar and then click Save script.

This is the end of the MySQL database design page.

#