Every year FileMaker developers converge on a specified city to learn how to make the most of the FileMaker Pro product. And although the format of the FileMaker Developer Conference doesn't change too much year after year in terms of training, presentations, breakout sessions, etc., the content does change because of the various new features available with the latest software release. And FileMaker 16 does have a lot of new features to boast about, including its impact on FileMaker Server. I attended a session that focused on some of the under-the-hood components of FileMaker Server.
It's one thing to read about enhanced software functionality, but it is something altogether different to see and conceptualize it first-hand. And one thing that particularly stood out for me was how FileMaker Pro retrieves data from the server. I've been working with the FileMaker Platform for more than 20 years and I understand the general concept of how data is transferred, stored and retrieved on a server, but this session really helped me to appreciate the process and think of how we maximize the performance of the systems we manage at The Support Group.
One Size Does Not Fit All
How you organize your data within your database is a very important factor to database development and management, but it also impacts performance. Performance isn't always top-of-mind when we sit down to layout our database because at that point we're more concerned about what data to capture than how fast the server will be able to retrieve it.
FileMaker server uses a tree form to store data so we can have a bunch of branches, leaves and twigs that sort of define the complexity of our database. That impacts how efficiently the server is able to retrieve the data. Conventionally speaking, it's best to have a lot of tables with as few fields, and by extension, data as possible. But, I think that really depends on the design and function of the server and database. FileMaker Server ignores empty fields so there essentially is no overhead or bulk in terms of having a lot of empty fields within a single table. If we don't expect to have data in every field of the table, one large table might suffice. On the other hand, if we expect to have data within every field of a table, it might make sense to organize the fields into multiple tables.
As an example, our client, Family Lives has a table with more than 900 fields. That sounds like a lot, but it really isn't when you understand that Family Lives is in the healthcare industry and the fields are designed to capture different parts of the body, ailments, treatments, etc. However, there isn't data within every field for each record so data retrieval is lightning fast because FileMaker Server ignores the empty fields. This design basically defies convention, but it works.
Incidentally, I also learned something new about the client side persistent cache - the local cache in FileMaker 16 expires within 21 days. It's learning experiences like these that make the conference a valuable experience for me.
