Concepts

ERDs and the real database

An ERD in NeoSQL is more than a drawing. It is where you design and change the tables of a real database. Knowing where your ERD changes are saved and when they reach the database helps you avoid two common surprises: saving and finding the database unchanged, and dropping a table you only meant to take off the diagram.

There is only one model

NeoSQL does not keep a separate logical model and physical model. The tables in an ERD are the same physical model that is created in the database, and the human-readable names (logical names) are descriptions attached to it. Keeping two models means that a change to one is easily missed in the other, and they soon drift apart.

  • The Physical mode and Logical mode buttons in the info panel only change which names are shown for the same model. They do not change tables or columns.
  • Logical names are not written to the tables created in the database. They are stored separately from column comments.

The three steps a change goes through

When you change a table in an ERD, the change goes through the three steps below. The first two save only to your NeoSQL project; the database changes only in the last step.

StepHowWhat is savedDatabase
Save the ERDThe Save button in the info panel, or Ctrl+S (Cmd+S on macOS)Table positions, notes, groups, display settings and the tables included. The design of changed tables is saved tooUnchanged
Save a table designThe Save button in the toolbar that appears above a selected tableThe columns, keys and indexes of that tableUnchanged
Apply to the databaseThe Apply Changes to DB... buttonDDL generated from the difference between the saved design and the database; only the statements you select are runChanged
  • Changes not yet applied to the database are shown in color inside the table: new columns are green (1) and changed cells are blue. The colors stay after you save, until you apply the changes. Virtual tables that do not exist in the database are not colored.
  • The orange dot to the right of the table name means there are unsaved changes. It disappears when you save. Hovering over the dot turns it into the × button that removes the table from the ERD.
  • The Save button in the info panel (2) saves the ERD and the design of changed tables to the project.
  • The Apply Changes to DB... button (3) does not run DDL right away. It first shows the DDL it will run; the database changes only after you select the statements and run them.

How tables get into an ERD

A table in an ERD either already exists in the database or does not exist there yet (a virtual table). How it got into the ERD decides what DDL is generated when you apply changes.

HowResultWhen applied
Drag a table from the object explorer onto the canvasA table that exists in the databaseOnly the changed parts are altered with ALTER
Place a table with the Add New Table toolA virtual tableCreated with CREATE TABLE
Paste DDL into the Import Tables from DDL dialogA virtual tableCreated with CREATE TABLE

Bringing database tables into an ERD is also called reverse engineering, and applying an ERD design to the database forward engineering.

Remove from ERD versus Delete Table

The two names look alike but do very different things.

Remove from ERDDelete Table...
What it doesTakes the table off this ERD diagramDeletes the table from the database
DatabaseLeft as it isIf you choose Flag Only (Keep in the database), the table is only marked and deleted at the next apply. If you choose Execute DROP TABLE (Delete from the database), the DROP statement is shown right away and then run
UndoDrag the table in again from the explorerUndo the mark with the Cancel Table Deletion button
  • Selecting a table on the canvas and pressing Delete removes it from the ERD. It does not delete the table.
  • A virtual table that does not exist in the database yet is removed immediately when you click Delete Table....

Relationship lines are foreign keys

A relationship line in an ERD is a drawing of the child table's foreign key (FK). Drawing a relationship adds the FK columns and constraint to the child table's design, and the actual FK is created when you apply changes. Selecting a relationship line and pressing Delete removes that FK from the design.

Things to keep in mind

  • Saving does not change the database. To apply your design to the database your team or applications use, you must run the DDL with the Apply Changes to DB... button.
  • The Refresh button in the toolbar reloads the current table definition from the database. It asks what to do with changes not yet applied (merge or replace). If you choose replace, those changes are all lost.
  • The Apply Changes to DB... button runs DDL against production databases too. Always check the DDL it shows before running it.