Unique indexes over several fields in Base by Zaweba

Duplicates are some of the most annoying mistakes in any database. The same member entered twice, the same order number used twice for one customer, the same pupil booked into the same class twice. Some of these can be prevented by making a single field unique. Others only show up as a combination: an order number can repeat across customers, but not within one. For that, Base by Zaweba lets you add an index over several fields, and make it unique.

You add these in the Rules dialog for a table. The single field options, indexed and unique, are in the table designer itself, next to each field.

Single fields first

Start with the simple cases. A field that must never repeat across the whole table, such as a membership number, an email address used for logins or a product code, can be marked unique in the table designer. A field you often search or sort by, such as a surname or a date, can be marked indexed so Base finds records quickly.

Most tables need only one or two of these. Don't index every field out of caution, since each index is extra work for the database each time a record changes.

When you need a combination

A unique index over several fields says that the combination must not repeat. An order number together with a customer is the example in Base's own notes: one order number per customer. Other common cases are a class and a pupil in a bookings table, so nobody is booked twice into the same class; a product and a warehouse in a stock table, so each product has one stock row per location; and a member and a year in a subscriptions table, so each member pays once a year.

In each case, neither field on its own is unique. A pupil books many classes, and a class has many pupils. It is only the pair that should appear once.

Adding the index

Open the table's Rules dialog and add an index, choosing the fields it covers and whether it is unique. If you only want faster searching on a pair of fields you often filter on together, leave unique off. If you want to prevent duplicates, switch it on.

Before adding a unique index to a table that already has records, check for existing duplicates. A query that groups by the fields and counts the records will show any pair that appears more than once. Sort those out first, by merging or correcting the repeats, then add the index.

How it fits with the rest of the design

A unique index sits naturally beside lookups. A bookings table usually has a lookup to classes and a lookup to pupils, and the unique index across those two lookups is what turns it into a clean many to many link. Seen in the relationships diagram, the bookings table sits between the two with a line to each, and the index makes sure each line pair is only drawn once in the data.

Indexes also protect every route records take into the database, so an import from a spreadsheet can't add a second copy of a booking any more than a form can.

Choosing carefully

Ask whether the combination really can never repeat. A customer might legitimately place two orders with the same reference if your references reset each year, in which case the year belongs in the index too. Getting it right takes a minute of thought, and it saves hours of cleaning duplicates out of reports later.