6 Replies Latest reply on Jan 22, 2017 1:17 PM by taylorsharpe

    A larger more complex database or several smaller databases?

    c0nsilience

      I know this is subjective, but as experienced professionals, do you prefer a single-file larger solution or a several smaller file solution?

       

      For example, let's say a service company wants a CRM/Scheduling/Service Order/Task Management solution.  Would you build all of those elements into one database or several smaller departmental databases?  Why?  What would be the pros and cons to each method?

       

      I've built both and tend to favor the "low hanging fruit" multiple database approach, but it largely depends on the scenario.  I'm curious to gain some perspective from other devs on the topic.

       

      Thanks!

        • 1. Re: A larger more complex database or several smaller databases?
          philmodjunk

          "database" is a fuzzy term. We could easily argue that you only have one database here. What you mean is should your solution consist of one file or many and that's not a simple question to answer.

           

          There are trade-offs with both options and there is more than one variation to how you might do this.

           

          My preference for a solution of any size is to use at least two files--one for all the tables and one for the interface--the layouts, scripts, value lists, etc. This enables me to swap out an interface file for a newer copy and not necessarily have to import any data to do so. Your relationship graphs in the two files can also be simplified as the relationships needed in the data file are not the same as those needed in the interface in many cases.

           

          Beyond that, multiple files can lend to modular design approaches such as mixing and matching different modules for different clients and being able to take down just one module for updates rather than the whole system.

           

          Where this becomes a problem for me is with accounts and passwords. Each file you add to your system requires its own set of accounts, privilege sets and passwords so the more  files you have, the more effort you have to put into managing security on each file.  Yes, you can set up scripts and some global fields to help you "synch" your account info from file to file, but only for FileMaker Accounts--not externally authenticated accounts and not for Full Access accounts.

           

          So you gain some and you lose some and the best option can depend on the scale of your solution. I manage a system of over 100 files with more than 300 users almost all of which have externally authenticated accounts. The scale of this solution precludes a single or two file design, but the multiple files do produce sessions of profound drudgery from time to time when we have to add a new account to the system.

          • 2. Re: A larger more complex database or several smaller databases?
            wimdecorte

            In addition to those arguments: the deployment itself can sometimes also benefit from having multiple files especially if you have some tables with data that does not change much.  FileMaker Server's backup mechanism can be made even more efficient that way.

            Having multiple files can also help in scaling the deployment by using multiple servers to host different files.

            • 3. Re: A larger more complex database or several smaller databases?
              c0nsilience

              "I manage a system of over 100 files with more than 300 users almost all of which have externally authenticated accounts. The scale of this solution precludes a single or two file design, but the multiple files do produce sessions of profound drudgery from time to time when we have to add a new account to the system."

               

               

              The use of groups in AD (if AD is what is being used) would certainly make this less painful.

              • 4. Re: A larger more complex database or several smaller databases?
                c0nsilience

                Thanks for both of your responses!  I value your insight and appreciate you taking the time to share it with me!

                • 5. Re: A larger more complex database or several smaller databases?
                  philmodjunk

                  We do use groups, but AD can't be used for full access accounts and it's still a major job if we need to add a new group to the system.

                  1 of 1 people found this helpful
                  • 6. Re: A larger more complex database or several smaller databases?
                    taylorsharpe

                    As noted above, there are a few reasons to break things into multiple databases.  But in general, the purpose of FileMaker is rapid application development of user friendly easy-to-use solutions.  Breaking things up just adds complexity (multiple securities, etc.) which I recommend avoiding.  Until you have some specific requirement making you to do it, keep it as one solution.  That is how I handle most of my client solutions.