Seeing how your tables link in the Base relationships diagram

A database with three tables is easy to hold in your head. Once you reach eight or ten, with customers, orders, order lines, products, suppliers and a few lists of choices, it gets harder to remember which table points at which. The relationships diagram in Base by Zaweba draws it for you. Every table is a box, and every lookup field is a line running from the table that holds it to the table it points to.

The lines are marked the way desktop database users will recognise. The many end carries the infinity sign and the other end carries a 1, so a line from Orders to Customers reads as many orders to one customer. Nothing about the picture is decoration. It is drawn straight from the lookup fields you have made in the table designer, so if a line is missing, the lookup is missing too.

Reading the diagram

Start with the boxes on the left. Those are usually the tables that other tables depend on, such as Customers or Products. Follow a line to the right and you reach the tables that refer to them, such as Orders, and then the tables that refer to those, such as Order lines. When you read it this way you can see the chain a record sits in, which is the same chain a form with a sub-form or a grouped report will follow later.

The diagram is also a quick check for mistakes in a design. A table with no lines at all may be fine (a simple list of settings, perhaps), but often it means a lookup was never made and a field holds a typed name where it should hold a link. Two lines between the same pair of tables can be right too, for example an invoice with both a billing contact and a delivery contact, but it is worth a second look to be sure you meant it.

Arranging the boxes

Drag any box to move it. Base remembers the positions in the database file itself, so the next time you open the diagram it looks the way you left it, and anyone you give the file to sees the same arrangement. That makes it worth a few minutes of tidying once a design settles down. Group related tables together, keep lines from crossing where you can, and put the tables you work with most where your eye lands first.

If the picture has become a tangle, Tidy the layout rearranges it for you. It puts parent tables on the left and the tables that point at them further right, which suits the left to right way most people read a chain of links. You can then nudge a box or two by hand to finish it off.

Adding a relationship from the picture

You don't have to leave the diagram to link two tables. Add relationship creates a lookup field from the diagram, which is handy when you are looking at the whole design and spot a link that ought to be there. Behind the scenes it is the same lookup field you would make in the table designer, so it behaves the same way everywhere: in the datasheet, on forms, in queries and in the website Base can export.

A good habit is to sketch the design in the diagram as you build it. Make the tables, add the relationships, then step back and look. If you can explain each line out loud ("each order belongs to one customer, each customer can have many orders"), the design is probably sound. If a line is hard to explain, that is usually the place to rethink before you type in hundreds of records.

Mistakes worth avoiding

The most common one is linking in the wrong direction. The lookup field belongs in the table on the many side, so the order holds the customer, not the other way round. The diagram makes this easy to spot, because the infinity sign should sit at the end where many records gather.

Another is leaving the diagram as an afterthought. It is a design tool as much as a picture, and opening it early saves you from building forms and reports on a structure you later have to unpick.

Keep it open in a corner while you design, and it will answer most "how does this connect?" questions before you have to ask them.