MariaDB database design

Quick start guide for MariaDB database design in Moon Modeler.

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

1. New project

To create a new project, click New on the toolbar and select the MariaDB project type.

2. Database tables and columns

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

New table

A new graphical object is shown in the diagram.

You can edit MariaDB table properties via Right Side panel:

Edit properties

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

Edit

Table properties

In section Details database table settings can be specified.

Table detail

Columns

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

New table column

Column specifics

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

MariaDB database design - column properties

3. JSON datatypes

MariaDB 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 new JSON to your diagram, defined JSON structure and then use the pre-defined structure as a data type for your database column.

database modeling

 

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

JSON

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

New JSON definition

Using JSON in table columns

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

JSON in MariaDB database design

 

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 relation details you can specify referential integrity, used key (primary key or alternate key) and cardinality.

MariaDB database design - Relations

5. Code generation

To preview SQL script for your MariaDB 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.

SQL script for MariaDB

This is the end of the MariaDB database design page.

#

Copyright: 2019 Datensen. All rights reserved.
This website uses cookies to ensure you get the best experience on our website.