How to draw a relationships diagram for your database

The relationships diagram in Base by Zaweba draws itself from the lookups you have already made. Every table appears as a box, and every lookup field becomes a line to the table it points at. You drag the boxes where you want them, and Base remembers the layout inside the database file.

So the real work is not drawing at all. It is getting the links between your tables right, and the diagram is the place where you can see whether you have.

What the lines mean

A lookup field in Base links a record to a record in another table. An order points at a customer, a subscription points at a member, a book points at its author. In the diagram each of those links is a line running from the lookup field to the table it looks up, marked as many to one: the infinity sign sits at the many end and a 1 at the other. Many orders, one customer.

Reading it that way round helps when you are planning. If you find yourself wanting a line that is many at both ends (students and courses, say), that is usually a sign you need a third table in the middle that looks up both.

Drawing the links from the diagram

You don't have to go back to the table designer to add a link. Add relationship in the diagram creates a lookup field for you, which then shows up in the table like any other field. That suits people who think visually: lay out the boxes, then join the ones that belong together.

Each lookup also carries a rule for what happens when the record it points at is deleted. Base can refuse the deletion, delete the records that refer to it as well (a cascade), or empty the lookup. It is worth deciding this while the diagram is in front of you. Deleting a customer and losing every order with it is rarely what anyone wants, whereas deleting an invoice and taking its lines with it usually is.

Making the picture readable

A new database with three or four tables is easy to follow. By the time you have a dozen, the lines start crossing. Drag the boxes so that tables which are looked up a lot sit together, and keep related groups (sales on one side, stock on the other) apart. The positions are saved in the file, so the diagram looks the same the next time you or anyone else opens it.

If it gets out of hand, Tidy the layout rearranges everything with the parent tables on the left and the tables that point at them further right. That gives you a clean reading order: start on the left with the things that exist on their own, like customers and products, and move right towards the records that depend on them, like orders and order lines. You can then nudge individual boxes from there.

Using the diagram as a check

Once it is drawn, look for tables with no lines at all. Sometimes that is fine, such as a settings table or a simple list. Often it means a link is missing and the same information is being typed twice in two places. Look too for a table with a great many lines coming into it, which is normal for something like Customers but odd for a table you thought was minor.

The diagram is also a good thing to have open when you build queries. The visual query builder joins tables along their lookups, so the lines you see here are the joins you will get there.

Where it fits

The diagram works alongside the other tools in Base by Zaweba rather than replacing them. Fields, types and rules are set in the table designer. Lookups show up as dropdowns in the datasheet and in forms, and an Auto form adds a sub-form for each table that looks the main one up. When you export the database as a website, the same links become foreign keys in the MySQL or MariaDB tables, with dropdowns on the add and edit pages.

Keep the diagram tidy as the database grows and it doubles as documentation, which is handy when someone else has to pick the file up later.