That's not an easily answered question. It depends a lot on exactly what the data in each table contain, use case, frequency of backup, etc.
However, it's often beneficial to separate into multiple data files in these circumstances:
1) You have externally stored container fields and you want to avoid re-importing them during migrations.
2) You have large amounts of infrequently-updated data and you want to make your backups run more efficiently. (Separate out the infrequently-updated data from the data that are more frequently updated. Makes optimal use of the hard links feature of FileMaker Server.)
3) You have multiple groups of users whose data are completely segregated from each other.
4) Other circumstances I haven't thought of at 5:45 AM.
Too generic a question.
Mike is asking all the right questions.
One benefit though of having multiple data files: if one of them is really hit the most you can potentially put that one on its own server and do some limited "load balancing" that way.
Agree with Wim. I've done this a couple of times for clients with very large data files that would not let me archive old data.
Additionally, I would ask the question: "How big is this solution going to grow?".
Another thing to consider regarding container fields is to place it within a separate FM data file, avoiding the challenges of dealing with external container data on the server when restoring from backups or saving offline copies.
This can be doubly effective if the container data changes less than the other data in the system, minimizing slowdowns during incremental backups.
Rather than being concerned with the number of data files in a Separation Model solution, work at keeping your tables narrow (low field count) so that data caching on the network is minimized. Another good reason to store container data separately from its related data, whether via external storage or in a separate FM file.
Hi and thanks
1 file GUI with 3 tables (100 fields) and 1 file DATA with 64 tables (total 5/6.000 fields, 800 relations). Total 160 MB
Seeing the size of your database, solution 1 would be just fine. Easier to reuse code/layouts etc.
I have solution 1 setups with 2 gigabyte files, but I must admit I also have real static data (million records of zipcodes/cities/country) in a separate file.
Solution 2 would be better voor backup performance, but with 160MB I wouldn'teven think about which solution is better.
For 160 MB I'd go with a single file solution...