Files vs. Tables

[benk]

I was looking for a bit of advice...is there a point when it is better to have seperate files instead of more tables.

What I mean is...is it better to have one file with 50 tables or 5 files with 10 tables each?

I have a fairly large db (300000 records) I am rebuilding.

[Reed]

There are a lot of different reasons to use multiple files/one file.

1. It might be useful to have an interface file and a data file, so that updates to the interface/scripts can be performed without exporting/importing data.

2. Keeping tables that need to be backed up frequently separate from large static tables might make it easier to perform backups and save space, as the backups can be separate.

3. Keeping everything in one file can make it easier to manage accounts and privileges without having to script across multiple files or use external authentication.

4. Access to custom functions is on the file level, so you have to duplicate the functions if you use multiple files.

This is just the tip of the iceberg, and I'm sure that there are people here more knowledgable than I who can give additional insight. You might want to read some of the briefs and whitepapers on filemaker.com, as some of them address these issues.

[benk]

I do understand the upside to having everything in one file...I was just wondering if there comes a point when it makes a performance difference to split things up.

Does anyone know if there are limitations to the number of tables in a file?

Edited August 22, 2005 by Guest

[comment]

Yes, there is a limit of 1 million tables per file.

[Matthew F]

Follow this link to see Filemaker's technical data sheet.

Edited August 23, 2005 by Guest

[Ender]

Hi Ben,

For a large solution, using a single-file design has several maintenance concerns and challenges. First, there are times when it is more convenient to work on database changes in an offline copy (either because the developer is not on-site, or the changes are significant enough that they would be difficult to implement while the solution is hosted.) To copy the data into the new version of the database would require separate import field mappings for each table in the file (and updating of the serial numbers.) The second maintenance concern is related; if file corruption occurs, the best solution is to recover the data into a previous backup. But if everything is in one file, this would require a long recovery process and a long import process to move the data. Third, some tables in a solution may be the type that get purged each year. Deleting hundreds of thousands of records from one table can be a very slow process, and using a clone requires all those imports for the other tables again.

There are also concerns about the performance of a large, single-file solution, as Harry points out in this thread:

http://www.maclane.com/ubbthreads/showflat.php?Cat=0&Number=496925&an=0&page=0#496925

For the developer, a single-file solution seems like the easiest way to develop; all the tables are available without adding external references, all the scripts are accessible without making an external call, and the security can be managed in one place. However, the relationship graph can quickly become a huge tangled web. The layouts may number in the hundreds (or thousands,) requiring lots of scrolling in the layout lists. And in order to grant and limit access to the different areas of the solution for all the different user types, lots of privilege sets may be required, each having to have very specific access rights to records and layouts.

For these reasons, I recommend a modular approach, where tables are grouped into the files by where they are mostly used. For example, a large solution might be broken down into an Invoicing module, a Contact Management module, an HR module, a Fixed Asset module, and whatever else you can think of.

Using modules allows one to perform those maintenance tasks on just the module that needs it. If a file develops file corruption, it may be possible to still run the other modules, closing the corrupt one for a couple hours while you get it repaired. If a module is replaced with a different version, the imports required will be limited to the tables in that file.

The relationship graph for a file only needs to show those TOs that involve that module. The Contact Management, HR, and Fixed Asset modules are all very important, but they probably have little to do with one another. Limiting what we see by grouping the tables into modules makes the relationship graph much easier to work with. Same kind of thing for layouts.

Security for multiple files can still be quite complicated, but it may be easier in some respects as the privilege sets in each file are only dealing with the tables in that module.

"Hub and Spoke" (modules) and other architectures for converted solutions are discussed in the Migration Foundations and Methodologies tech brief on filemaker.com:

http://www.filemaker.com/support/upgrade/techbriefs.html

[benk]

Thanks Ender...this is what I was looking for...

I thought this would probably be the case, I just wanted confirmation from someone who has been there with FMP7.

Thanks again for the help.