I have seen databases with a lot of tables. The one i’m currently working on has only two tables. Im new to this as well. I’m stuck on script writing, do you have any experience with scripting.
There's a limit of 1 million tables per file. Its also about the size of the file and the disk space it is sitting on. FileMaker has a limit per file of: "
I think people start separating tables into different files if one set of tables could get exponentially larger than the other ones. Like an orders table and order lines table might be in a different file than the products because orders will get so much bigger so much faster. But that's not a requirement. I myself developed my first database with 242 tables in it. The size of the file grew to be 1.32Gb, but that was my first system and may not be properly structured (I've learned SO much since then).
Separating tables into different files comes at more of a development cost: keeping accounts synced in files, making sure the files are linked properly, making changes to one or both files, etc.
However I do know of systems with many different files. Those systems, tend to be highly complex systems with entire different modules. I've not built one of those. I'd guess that most of us are fine with one file.
This system will end up being quite large with many tables in the end. I just wanted to be sure from the start that I'm not heading in the wrong direction.
I don't think its too difficult to switch to a file-separation model if you feel the need down the road. There's a process out there that you must follow in order to not break anything. Hopefully people will chime in about it, or I can test out what I think is the process and report back.
But that may be far down the road for you. The data is what makes a file grow larger and larger.
EDIT: Here's a video about this process: FileMaker Separation Model: Splitting Files - YouTube by Todd Geist. This can work to help answer.