I am in the process of building a very large and complex enterprise solution for my company and have a decision to make on whether to use a single or multi-file approach. It seems that a common approach for large solutions is to use separate files for data-related tables and interface-related tables. I've heard that some developers sometimes use separate files for very large data tables that grow over time, and also that it may be a good idea to use a separate file for tables that contain information that needs to be more secure. I'm hoping to get some insight regarding the pros and cons of different approaches, especially regarding the various development costs and benefits, performance issues, security issues, etc.

Hi. I'm a big fan of multiple files (both multiple interface files and multiple data files). The biggest downsides to this approach include

Having to write out your Access Privileges in each file (though you can write FileMaker scripts to write all but the Full Access accounts for you).

It can be very difficult to GTRR or to move a found set from one interface file to another interface file (moving from interface files to data files is easy).

Some redundancy as utility scripts and custom functions are repeated in multiple files.

Increased hosting fees if your host charges per file.

At the same time a lot of the supposed advantages to separating interface and data don't really seem to pan out in the real world. For example, *most* new features in a solution will require changes to both the data and interface files so the idea that you can just load new interface files as you rev your software doesn't seem to happen, in my experience. And while multiple interface files do hold out the promise of having different developers work on different files at the same time, I can tell you its hard enough managing a single developer.

So for me the big advantages to multiple files come down to not having all your eggs in one basket when files go South and reduced import overhead when you have to load new versions of data files.

I am thinking of using FileMaker as a front end client over a WAN, and separating the interface into a separate file and storing it locally so all that needs to move over the network is the data. Obviously this means any change to the interface will result in having to make the changes in multiple places, but the hope is to have a performance gain since we would be transferring much less over the network. One of my primary concerns in using a multi-file approach is the additional time it may take to develop a solution this way. John, do you think I would see a performance gain over the network by storing the interface files locally? Do you find by using a multi-file approach that it takes significantly more development time?

kennedjw wrote:John, do you think I would see a performance gain over the network by storing the interface files locally?

Yes you will. We used to do this a lot in the FileMaker 6 days (it was also a great way to prevent record locking). However, there are a few other things to consider. For one, FileMaker does a good job of caching interface elements (.jpegs, etc.) so I think most of your speed gain will be on the first screen loads- not to be underestimated, but I think this can also be dealt with by managing your user's expectations: providing a status bar during launch for example. In general this client-side design will not deal with the real speed problems user will have-- fetching and sorting data on the server: designing with that in mind and making sure you don't go to the server more often than you need to will result in more long term speed benefits.

At the same time, you give up a lot when you move your interface and logic files off the server. The whole we-can-easily-make-changes-because-this-isn't-Oracle thing pretty much goes out the window as almost all changes will require distributing new front end files. (Check out SyncDek for an cool solution to this problem.) From a security standpoint you are also granting people physical access to the file- making it much easier to crack.

So, as a compromise, I might consider a local file that had your interface graphics, but not your interface per se. You can even extend this to include anything that is large and static- like zip code or some geocoding files, for instance. Use an auto-enter account on this local file--one that does not replicate an account or password in the served solution so that it is the served solution that does the actual authentication.

Do you find by using a multi-file approach that it takes significantly more development time?

No. I don't. While there is redundancy in the multi-file approach, we can copy and paste so many objects from file to file (scripts, script steps, tables, etc.) that it isn't a problem. As I mentioned earlier, however, there is some extra effort needed in the scripts required to write your access privileges / re-logins across multiple files and the difficulty of using GTRR to move form one interface file to another.