Walkthroughs

Design your first ERD and apply it to a database

You create a project in the desktop app without an account, design the demo store's customer (customer) and order (orders) tables in an ERD, and create them in a real database. It takes about 15 minutes, and all you need is the installed desktop app. To avoid running a separate database server, it uses SQLite, where a single file is the database.

Where you can use itWebDesktopWithout signing inSigned inLocal projectsAccount projects

Follow the SQLite example in the desktop app. To follow along in the web app or with another DBMS, enter that database's connection details in Create and edit a connection and do everything else the same way.

Before you start

  • Install the desktop app (Install and update). When you first open it, the home screen appears without signing in (First run).
  • Decide on a folder for the SQLite file. If you use another DBMS, use a practice database where you are allowed to create tables. The last step actually runs CREATE TABLE.
  • The flow is easier to follow if you know that a change made in an ERD goes through two steps: saving and applying it to the database (ERDs and the real database).

Creating a local project

  1. Click the Create New Database Project card on the home screen. Result: The New Project dialog opens.
  2. Enter Demo Store in the Project Name field (2).
  3. Leave the Create Sample ERD (classicmodels) option (3) unselected and click the Create button (5). Result: An empty project with no connection is created as a local project that is stored only on your device, and the project screen opens (Sign-in and project types).

If you choose the sample ERD, you start with an ERD that already contains example tables. If you do not, an empty project is created. These steps build the tables yourself.

Creating a connection

To create an ERD's design in a database, you need a connection to that database. That is why you create the connection before the ERD.

  1. Click the Add connection button in the middle of the explorer on the left. Result: The connection dialog opens.
  2. Enter shop-db in the Connection name field and choose SQLite in the DBMS field (3).
  3. Click the Connect to DB button. Result: The connection fields (5) appear.
  4. Enter the full path of the file to create in the Database File field (for example, shop.db in the folder you chose). To use an existing file, pick it with the Browse... button. Result: The JDBC URL field (6) follows along.
  5. Click the Test Connection button (7). Result: The Connection succeeded. notification appears. If there is no file at the path you entered, the SQLite driver creates an empty database file at this point.
  6. Click the Save button (9). Result: The shop-db connection and its main schema appear in the explorer. There are no tables yet.

What each field means, and the connection fields of other DBMSs, are covered in Create and edit a connection.

Creating an ERD

  1. Click the ERD button in the ERD and SQL list at the bottom left. Result: The Create ERD dialog opens.
  2. Enter Order management in the ERD Title field (1).
  3. Leave the Connection field (2) at shop-db and choose main in the Schema field (3).
  4. Click the OK button (4). Result: An empty ERD bound to the main schema of shop-db is created and its ERD tab opens. New tables you place in this ERD are designed as tables of the main schema (ERD and SQL list).

Drawing two tables

  1. Click the Add New Table button (3) in the toolbar, then click an empty spot on the canvas. Result: A table1 table appears. It is a virtual table that does not exist in the database yet, and it already has the primary key column id from the connection's template.
  2. Click the table to select it, and change the table name in its header to customer. In the column grid, rename id to customer_id.
  3. Add columns with the + button in the grid header to create email (VARCHAR(200)) and name (VARCHAR(100)), and turn on their N/N cells. Result: The new columns are shown in green.
  4. Repeat steps 1 to 3 to create the orders table. Rename its primary key to order_id, and add status (VARCHAR(20)) and ordered_at (TIMESTAMP). Result: The design of both tables exists only in the project, and shop.db is unchanged.

What each cell means, default values and changing the column order are covered in Table node.

Drawing the relationship

An order belongs to one customer, so orders gets a foreign key that references customer. Draw relationships before you apply to the database for the first time. That way the foreign key goes into the CREATE TABLE statement itself. SQLite cannot add a foreign key to an existing table with ALTER TABLE, so if you draw it later, NeoSQL cannot generate the DDL and leaves only an explanatory comment.

  1. Click the Non-Identifying Relationship button (4) in the toolbar.
  2. Click the parent, customer, then the child, orders. Result: The Add Foreign Key dialog opens.
  3. Because orders has no customer_id column, the New Column switch is on and the new column name is customer_id. Leave it as is and click the Add Foreign Key button. Result: The customer_id column and a foreign key are added to the design of orders, and a relationship line appears between the two tables. The relationship line is the foreign key.

Saving

  1. Click the Save button (2) in the info panel, or press Ctrl+S (Cmd+S on macOS). Result: The ERD's layout and the design of both tables are saved to the project. The database has not changed yet, so the green color (1) stays.

Applying to the database

Applying from the info panel puts every table changed in the ERD into one dialog, and the referenced customer is created before orders. If you apply only orders from its own toolbar, the table it references does not exist yet, so the Referenced Table Missing dialog asks first.

  1. Click the Apply Changes to DB... button in the info panel. Result: The DDL Preview and Execution dialog opens.
  2. Check that the table list (3) contains customer and orders with the CREATE mark.
  3. In the DDL preview (4), check the two CREATE TABLE statements and the FOREIGN KEY of orders.
  4. Click the Execute DDL button (6) and confirm in the confirmation dialog. Result: The selected DDL runs against shop.db and the two tables are actually created. NeoSQL reads the created tables back from the database and saves them as the design, so the green color disappears.

The dialog's options, and what to choose when an error occurs during execution, are covered in Apply changes to the database.

Checking the result

  1. In the explorer, click the refresh button of the main schema and expand the Tables group. Result: The explorer reads the list from the database again, and customer and orders, which you just created, are listed as tables in the real database. If the names are in the normal color rather than green, the database and the design are the same.
  2. Expand orders and check that it has the customer_id column. Double-click it to see the foreign key as well in the Table tab.

What's next