4 Replies Latest reply on Oct 21, 2015 8:56 PM by jbrown

    Separate Databases or just add tables?

    S.Williams

      So what is the "best practice" on deciding when to use another database or just add more tables to a single database?

      And how many tables is too many in a single database?

       

      for example: say I have a CRM project that has a database with 12 tables

      I now want a project management system that needs another 12 tables but it could also use a table or 2 from the CRM system.

      Then later I need a Venue management system that also has a bunch of tables but could use tables from above also.

       

      In general I guess I'm asking, when is it time to create another database -vs- just adding more tables and what considerations are there.

       

      Thanks!

        • 1. Re: Separate Databases or just add tables?
          shelton356

          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.

          • 2. Re: Separate Databases or just add tables?
            jbrown

            S Williams,

            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: "


            Technical specifications of FileMaker Pro 14 and FileMaker Pro 14 Advanced | FileMaker

             

            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.

            • 3. Re: Separate Databases or just add tables?
              S.Williams

              Thanks Jeremy,

              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.

              • 4. Re: Separate Databases or just add tables?
                jbrown

                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.