Building a Tidy Database – An Overview

In this new series of articles, I am going to explain all the methods I use to help maintain a tidy and manageable database. As the size of a database grows, it can be very easy to fall into complacency, and become slack in areas such as naming conventions. I will give some tips on how to avoid this and keep your database spick and span

 

Why a Tidy Database?

Keeping your database tidy has huge benefits over the entire life cycle of the system. If you are building a database for the first time, or for a personal project, then you might think this is not important. Indeed, while you are in the development phase, you are “in the moment” – you know where everything goes, how everything works, and why everything is the way it is. Only later on down the track will it become apparent to you why you should have started keeping things tidy from the beginning. You might think you’ll never forget how your database works, but I guarantee that if you leave it for 6 months, it will feel like someone else built it when you return to do more development! The problem is people are continually learning, continually coming up with new ways to do things in FileMaker. The way you build a database 6 months from now may be completely different from the way you build a database now.

For this reason, it is important that you devise some simple conventions you can follow in the development of your database. If you maintain this consistency through your databases, then it becomes much easier to return to a database after a period of time, and be able to understand how it works.

For a developer, this is even more important. If you have multiple developers working on a project, then ensuring everyone is on the same wavelength when it comes to conventions is one of the most important things you should make sure of. Failing to do so usually results in everybody bringing their own ideas to the database, and the whole thing ends up in a mixture of conventions.

Most importantly, maintaining a tidy database is a great habit to get into. If you start early, and force yourself to stick to conventions, then you are well on your way to becoming a great developer!

 

What is a “Tidy” Database?

One of the biggest things that a tidy database contains is consistency. The term consistency encompasses a broad range of areas of the database, some examples are:

Consistency in naming conventions for tables, fields, table occurrences etc…

Consistency in layout design elements, ie fields, labels, portals all built to a similar standard.

Consistency in calculation design and scripting, ie commenting, spacing, common actions..

Now, being consistent does not necessarily mean being tidy. You can still have a database with consistently bad naming conventions! The goal is to find good naming conventions, and standards that you can understand and feel are easy to understand, and then stick with them! It’s no good switching your conventions halfway through development of a database unless you are prepared to change the conventions everywhere within the database.

 

What’s to come?

Within the coming weeks and months I will be writing articles (in order) on the following topics for this series:

Anchor-Buoy: An alternate approach to the relationship graph

Naming Conventions and good Practice: Database Schema

Naming Conventions and good Practice: Scripts

Naming Conventions and good Practice: Value Lists and Custom Functions

Consistency in Design: Building Layouts

Plus anything else that might crop up.

You may be asking yourself what Anchor-Buoy is. Well in a nutshell it is a design methodology for how to structure your relationship graph efficiently. Most beginners to FileMaker start with a few tables, and some table occurrences on their graph to match the tables. They will create some basic relationships between the table occurrences, but generally won’t go beyond a certain level. If the database ever begins to grow in size and complexity, they might become overwhelmed with the number of relationships and table occurrences on the graph, and more often than not, the graph evolves into a classic “spiderweb” structure, with everything related to everything else, one big mess!

Anchor-Buoy breaks the cycle of the spiderweb graph. Hopefully I can do it justice and explain it sufficiently, because I believe it to be the single most significant thing you can use to help keep your database tidy and manageable going forward.

With Anchor-Buoy explained, I will move onto discuss the naming conventions I use for table and field names, and also the naming of table occurrences on the graph. How my table occurrences get their names is specific to having an anchor-buoy structure, which is why I must explain that first.

Next, I will go over some of the ways in which I keep scripts well organized. You might not think it necessary, but alot of larger FileMaker solutions contain over 500 scripts, so it pays to know how to locate them. I will also talk about some things you can do within your scripts to keep them tidy and more readable.

Then I will talk about some other conventions that apply to more specific areas, such as value lists and custom functions. While perhaps not quite as important as other naming conventions, that is no excuse not to slack off when naming them!

Finally, I’ll explain and demonstrate the importance of consistency when it comes to designing and building your layouts.

Stay tuned for more soon!