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 toolbar and select MariaDB project type.

2. Database tables and columns

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

New table

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 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 datatype for your database column.

database modeling

 

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

JSON

Then you can define details in right side panel or in 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 Datatype selectbox.

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 toolbar and then click parent and then child table.
When new relation is created, foreign key appears in 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 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 is not endorsed by MongoDB, Inc.
This website uses cookies to ensure you get the best experience on our website.