6 Replies Latest reply on Sep 13, 2012 3:35 PM by philmodjunk

    One big database or many small ones?



      One big database or many small ones?


           I'm sorry if this question is stupid, but I cannot seem to find any answers to it anywhere...

           How do we decide that our solution would be better served by having many small databases linked together instead of a big one, with many many tables... ??

           Thank you...



        • 1. Re: One big database or many small ones?

               In the FileMaker world, "database" is a very fuzzy term. It can refer to a single table, a single file with one or more tables or multiple files, each with one or more tables.

               Your question isn't one with a simple answer as the needs of your particular situation will have much to do with whether or not you should keep all your tables in one file or one table to each file.

               In general, it is simpler to keep all your tables in one file--this is, in fact, a feature that was requested for quite a few years by FileMaekr developers until it finally became possible with FileMaker 7. It simplifies managing security settings as each file must have its own set of accounts and passwords to control access. Managing other aspects of your design will be simpler as some other parts may need duplication in more than one file.

               But that one file of tables may not be the only file in your solution. Some developers use a Convert to Seperation Model where the layouts, scripts, value lists etc are in one file and all data tables are in another. The advantage to this approach is that many updates to your database only require changes to the interface file--not the tables. In those cases, updating a working database becomes a simple job of replacing the old copy of the interface copy with the new copy and no importing of data is necessary. Only changes to the design of the data file would then require importing data.

          • 2. Re: One big database or many small ones?

                 Thank you for the fast answer.

                 Is there any size related advice for the choice I'll make? At how many tables and fields should I worry about how fast the solution will work?

                 Thank you again!



            • 3. Re: One big database or many small ones?

                   FileMaker Files can become very large and still function. The number of records and the size of them will have more effect on overall file size as would embedding large files into container fields. Splitting up your files with some tables in one and some in another vs all tables in one file will not have much impact on performance. The design of your database and the number of records in your tables will have far more impact on performance.

              • 4. Re: One big database or many small ones?

                     One other point is to consider how the table is used. For instance a Filemaker Error Codes table might be a standalone file as could be a table of Zip Codes or names of the states. Or the tables could be standalone for development and sharing and imported into your megafile.

                     And then there is the update question. Since an external Filemaker file can act as if it is inside the megafile, how do you provide updates to this file in the least difficult manner possible for your remote users on IOS devices, etc?

                     If that table is actually a unique file, then you simply copy that new file to each device and replace the old one being careful to first close it on that device. This method is quite useable for updating catalogs, price lists and other changeable files.

                     So, there is no one RIGHT answer but as my dad always said and it drove me nuts, "It Depends..."

                     Newcomers might not be familiar with the idea of external files and TOs made from them and how Filemaker so nicely adjusts when you replace one of those external files with a new copy. Just remember to close it first, especially and definitely on Filemaker Server.

                • 5. Re: One big database or many small ones?

                       One reason I put files together is so one password opens all files (corrected). Otherwise, when I add a new user, I have to add account to each file seperately.

                       One way it stinks to have them together is all your scripts and layouts are all in one huge heap. It get's difficult to work with all the layouts and scripts in one table.

                       I would say the more complex the database, the more files you would want to have to improve readability, though I don't think it actually affects perfomance.

                       In general, for convenience sake, I will include subsidiary tables in a file with a more central table if it is not going to have many layouts and scripts and it's never going to.

                       I make a new file when for readabilities sake, I don't want the visual clutter in my primary tables.


                       The short answer is to make it one file. Unless you know it is going to be a complex system. Either way you can always move the data around later so no harm.

                  • 6. Re: One big database or many small ones?

                              It get's difficult to work with all the layouts and scripts in one table.

                         Hmmm "table" must be a typo or you don't mean a "data" table but the list of scripts in Manage Scripts. You can have many tables in one file, after all. I find scripts are easier to manage when they are all in one file than when they are scattered throughout many files. Being able to group your scripts into folders--I often use folders to groupby the layout or set of layouts for which they were desibed combined with a search tool that helps find scripts by name makes this all pretty manageable.

                         And one way to make the management of accounts in multiple files easier is to use scripts to manage the creation of new accounts and changes to alld accounts in each additional file.

                         Regardless of the descisions made with regards to one file or many, the anchor buoy method can be a very useful way to organize relationships graphs to make them easier to read and work with.