I have a large system that used to be all in a single file since then I have moved to a seperation model with 1 interface file and many data files. Each data file has 10 - 40 tables. I have not noticed any major speed differences between the two models, but it has made updates significatly easier.
Ray Cologon has a good discussion about the pros and cons of data separation in the FileMaker Pro Bible. I suggest you read this and anything else you can locate on the issue, then think long and hard before making your decision.
I have one system where the original developer split the majority of the data tables out from one data table that does a mass import at least once a day, due to having a lot of indexing and corruption issues. Once I helped him set up the import as an update-append (i.e., update matching, add non-matching), the indexing and corruption issues went away. (And this was a while ago.)
All other systems I have put all the data tables in a single file. I've not seen any issues with doing it that way. I think, from a maintenance standpoint, it's preferable not to have multiple data files, since you have a lot of stuff that has to be duplicated:
- Relationships graph
Further, you can run into issues with hosting space on your server. If you have 80 files in your system, that eats up about 2/3 of a server's available file quota. Doesn't leave room for much else.
So, to answer your question, there's not much of a driver to split data tables out between multiple files, and pretty compelling reasons to merge them. At least in my experience.
I do agree with Mike that when possible I too prefer a single data file. My multiple data file situation is unique as it is really multiple databases ( customer service, engineering, receiving...) with a requirement to have a single interface file. In this situation it just made sense to have each segment with its own data file. Accounts is the biggest pain since Filemaker doesn't have a built in way to move accounts file to file meaning its my job to make these tools or do it all manually.
Well it depends. I splited a 11GB data file into 6 smaller ones. The performance is significantly imporved. It is way faster than before especially on iPhone and iPad.
I tool a multi-file .fp5 system and rearranged it into several data files with one interface file. I made my decisions based on grouping some tables together that had very few changes (accounts, preferences, country list, state list, value list table), and leaving others with a lot of changes - and thus more opportunity for corruption - separate from each other. I like to use the example of a library system that might have one file for the items owned, and one file for the borrower records. You can easily swap in an empty clone of the borrower records file once it is getting unwieldy or it is simply unnecessary to keep track that person x borrowed book y 7 years ago, especially if the book was sold last year at the book fair, and isn't even in the library anymore.
You can get the benefits of being able to update an interface file without touching the data, and depending on future needs, you could set up a 'localization' table in its own file so that you can easily have an on-site/off-site(offline)/multiple-site versions that can be integrated later on. If this is even a far-off possibility, read up on SyncDek and other methods of synchronizing multiple FileMaker systems to help you prepare for that now. (n.b. start using the uID function for every record in every table).
And Nick is right, if you go this route, you need to set up your own method of creating an updating accounts in each file with the appropriate script steps and a place for an 'admin' to add/remove people from the system. That said, if you can do it for two files, you can arrange it to work for more than two files.
-- Drew Tenenholz