DbSchema Relational Data Editor Guide

You have one order id and you want the rest of the story around it: the customer who placed it, the lines on it, the shipment that carried them. In a SQL editor that is three queries, and between each one you copy an id out of a result and paste it into the next WHERE clause. Writing it as one multi-table join instead only moves the work into the SQL. The Relational Data Editor in DbSchema replaces that with a click. Related tables open as panes side by side, and selecting a row in one refilters the panes under it to the rows that belong to it.

Access the Relational Data Editor

There are two ways in. From the Editors menu, choose New Relational Data Editor to open an empty one and pick the starting table yourself. Or right-click the header of a table in the diagram and choose Open in Relational Data Editor, which starts you on that table. On the diagram there is a third shortcut: hold Shift+Ctrl and click a table header, and DbSchema opens the editor on it without leaving the diagram.

The editor appears in the Tools panel at the bottom of the screen, under the diagram, so the diagram stays visible while you read rows out of it.

An editor you have assembled is worth keeping. Close one and DbSchema asks whether to preserve it in the design model for later use or drop it permanently, so the panes you lined up come back the next time you open the model. To get rid of one on purpose, right-click it in the structure tree under Diagrams and choose Drop.

Create foreign keys and virtual foreign keys

The editor moves from a parent table to a child table along a foreign key, so what it can reach depends on what the schema declares. Where the foreign keys exist, there is nothing to set up.

Where they do not, draw them yourself. Hover the referencing column on the diagram, and a connector handle appears on its right edge; drag from that handle to the target column in the other table. DbSchema then asks whether you want a real foreign key or a virtual one, and the answer decides where the change lands. A real foreign key is a schema change, so a connected DbSchema executes it against the database. A virtual foreign key is written only to the .dbs model file and creates no constraint in the database, which is what makes it usable on a legacy schema you must not alter, on MySQL MyISAM tables, and on MongoDB. Either way the line is drawn on the diagram, and the Relational Data Editor follows it from parent to child.

Descend into further tables

Each pane carries a foreign key button on its table header panel. Click it and DbSchema adds the child table as another pane below, showing only the rows that match the row selected above. Click a different parent row and every child pane reloads against it. There is no limit on how deep this goes: a grandchild pane refilters when its parent pane changes, which refilters when the pane above that one changes.

Filtering works inside a pane too. Click a column header to open the filter dialog for that column, set a condition and a value, and the pane narrows to the matching rows. Several columns can carry a filter at the same time, so you can hold a date range on the parent while you read the children — the same narrowing you would otherwise build as a query.

Edit table records

Double-click any cell to change its value in place, or click Edit for the same inline change. The table footer carries Insert to add a row and Delete to remove the selected one. Insert opens an edit form where you fill in the fields of the new row.

Nothing reaches the database until you commit. Click Commit to write the pending inserts, updates and deletes, or Rollback to throw them away. These are row changes, so they go to the live database and never to the .dbs model file, which is the opposite of the virtual foreign key above.

Add and view multimedia files

A blob column shows as bytes, which tells you nothing about the invoice PDF or the product photo inside it. Right-click the cell, choose View Data, and give the file extension the content should be treated as, for example .jpg, .pdf or .docx. DbSchema writes the value to a temporary file with that extension and hands it to whichever application your system has registered for it, so the photo opens in the image viewer and the invoice in the PDF reader. The same dialog uploads a file from disk to replace the stored value.

Relational data browse is a Pro feature, and the download comes with a 15-day Pro trial. Get DbSchema at dbschema.com/download.html, connect to your database, right-click the busiest table in the diagram, and choose Open in Relational Data Editor to see what hangs off it.