"database" is a fuzzy term. We could easily argue that you only have one database here. What you mean is should your solution consist of one file or many and that's not a simple question to answer.
There are trade-offs with both options and there is more than one variation to how you might do this.
My preference for a solution of any size is to use at least two files--one for all the tables and one for the interface--the layouts, scripts, value lists, etc. This enables me to swap out an interface file for a newer copy and not necessarily have to import any data to do so. Your relationship graphs in the two files can also be simplified as the relationships needed in the data file are not the same as those needed in the interface in many cases.
Beyond that, multiple files can lend to modular design approaches such as mixing and matching different modules for different clients and being able to take down just one module for updates rather than the whole system.
Where this becomes a problem for me is with accounts and passwords. Each file you add to your system requires its own set of accounts, privilege sets and passwords so the more files you have, the more effort you have to put into managing security on each file. Yes, you can set up scripts and some global fields to help you "synch" your account info from file to file, but only for FileMaker Accounts--not externally authenticated accounts and not for Full Access accounts.
So you gain some and you lose some and the best option can depend on the scale of your solution. I manage a system of over 100 files with more than 300 users almost all of which have externally authenticated accounts. The scale of this solution precludes a single or two file design, but the multiple files do produce sessions of profound drudgery from time to time when we have to add a new account to the system.
In addition to those arguments: the deployment itself can sometimes also benefit from having multiple files especially if you have some tables with data that does not change much. FileMaker Server's backup mechanism can be made even more efficient that way.
Having multiple files can also help in scaling the deployment by using multiple servers to host different files.
"I manage a system of over 100 files with more than 300 users almost all of which have externally authenticated accounts. The scale of this solution precludes a single or two file design, but the multiple files do produce sessions of profound drudgery from time to time when we have to add a new account to the system."
The use of groups in AD (if AD is what is being used) would certainly make this less painful.
Thanks for both of your responses! I value your insight and appreciate you taking the time to share it with me!
1 of 1 people found this helpful
We do use groups, but AD can't be used for full access accounts and it's still a major job if we need to add a new group to the system.
As noted above, there are a few reasons to break things into multiple databases. But in general, the purpose of FileMaker is rapid application development of user friendly easy-to-use solutions. Breaking things up just adds complexity (multiple securities, etc.) which I recommend avoiding. Until you have some specific requirement making you to do it, keep it as one solution. That is how I handle most of my client solutions.