SQLite database design

Quick start guide for SQLite 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 SQLite table properties via the Right Side panel:

Edit properties

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

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.

New table column

Column specifics

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

SQLite database design - column properties

3. JSON datatypes

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 for sqlite
database modeling for sqlite - JSON in ERD

To add a 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.

Using JSON in table columns

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

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

SQLite database design - Relations

5. Code generation

To preview the 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 SQLite

This is the end of the SQLite database design page.

#