What's best? Single or multiple files?

[CharlesS]

I'm in the very beginning stages of designing a database to run my business. There probably will be between 15 and 20 tables in the final design, all related in some way. At least two of the tables will be relatively large, with over 10,000 records each. What are the advantages/disadvantages to keeping all the tables in one file or making a separate file for each table?

I have only recently upgraded to version 8.5, so previously I have always used separate files.

[Fenton]

As you've probably gathered already, there is no "best" way to do this that applies to all situations. The basic advantage of 1 file is that everything is there. The disadvantage is that everything is there -]

First, I don't think that 10,000 is really a lot. Over 100,000 starts to be a lot. 10,000 may be large in file size, but that will not affect operational speed as much as large numbers of records. So that is not a deciding factor.

Secondly, separate files does not really make it a lot "safer." If the files crash badly, they are likely all open, so the possibility of damage is spread across all anyway. Though of course if one is damaged to the point where it will no longer even open, then it is a consideration. But that would be very rare in a decent environment.

It is much easier to write scripts if they do not cross files. Of course, you can also just add a Table Occurrence of the external file to the local file, and a layout. But it's easier to keep these interactions all within the same file.

It is probably easier to open separate windows if you have separate files. But with the New Window step, you can also do that within 1 file.

If you're considering using the "separation of data" method then it's easier to have 1 interface file and 1 data file.

The main disadvantage of 1 file is organization of its many elements. The worst was always the scripts. But FileMaker 9 has finally added "folders" for scripts, and while I have some reservations about the way they've done scripts, it solves the problem of too many scripts in the list.

Layouts are still a bit of a problem however; no folders for layouts, as yet. The Relationship Graph is also going to be larger with a 1 file structure. Though there is less "redundancy" than multiple files.

It is important to use an "anchor-buoy" table occurrence group organization method. And you should do this right from the start, or else your layouts will be misassigned. Same for a naming convention for table occurrences.

The above will let you have quite a large Relationship Graph that is still fairly easy to manage. You'll need a decent size monitor.

[CharlesS]

Thanks for the response. I watched a short video about the separation of data and interface and that seems to make sense for my application. Could you explain what you mean by the "anchor-buoy table occurrence group organization method"?

[Fenton]

Anchor buoy and naming conventions; FileMaker Inc.

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

http://www.kevinfrank.com/anchor-buoy.html