9 Replies Latest reply on Feb 16, 2010 9:57 PM by RickWhitelaw

    Two databases or keep growing the current one?

    user14360

      Title

      Two databases or keep growing the current one?

      Post

      Hi,

       

      I have built what has become quite a complex database (to me anyway!), tracking information about a variety of different companies related to my business.

       

      It has 39 tables, 336 relationships, 152 layouts, 71 value lists and 296 scripts.  It's 20 MB in size, but isn't in actual use yet.  (There are about 2000 records.)

       

      I am looking at expanding it to incorporate another aspect of the business, and this could potentially be quite a large addition to it.

       

      I am wondering if people have thoughts on the best approach.  Do I continue with this file and benefit from all the relationships currently established?  Or do I start a new database file and link them up together? 

       

      Does anyone have any experience with the file capacity in Filemaker?  There is a theoretically large file size potential, but what is the actual impact on performance once files start becoming large?

       

       

        • 1. Re: Two databases or keep growing the current one?
          philmodjunk
            

          In terms of database function, either approach will likely work. In terms of managing the design and further updates, I'd recommend at least considering splitting your files up into a few logical modules and perhaps a data separation approach.

           

          The main purpose of splitting your system in to modules is so that each module is easier to work with when analysing/modifying the layout. Think of how separate Relationship graphs might have a lot less "spider webbing" just to give you one example.

          With the data seperated from the interface, many updates you might need to make can be done merely by swapping out the old inteface file for the new without having to perform a lenghthy import of records.

          • 2. Re: Two databases or keep growing the current one?
            RickWhitelaw
              

            Hildy,

             

            My business runs on a solution of eight different files (including a "Help" file). From one POV I see this as an advantage. It's modular, and when working on an aspect that's specific to one of the files, it's clear. One disadvantage: there are eight relationship graphs. A few are simple and a few are quite complex. There's necessarily "duplication" of relationships from graph to graph. Another: custom functions must be entered on a per-file basis. And one more: scripts that initiate actions in other files often require "sub-scripts" in the other file. A simple example would be you can't "GoToLayout" if the layout is in a different file. I think you'll adapt to whatever method you choose. As I learn I tend to learn things relevant to my particular situation (multi-file). And finally, FM doesn't care which file "owns" a given table as long as it's related properly.

             

            Rick. 

             

            Edit: My solution uses around 240 tables (not TOs) across 8 files. The files, including whatever data in them (moderate(?) amount) weigh in at around 20MB. This doesn't seem an unusual size. And compacted they'll lose around 2-3MB. 

            • 3. Re: Two databases or keep growing the current one?
              user14360
                

              Thanks for both of your responses.

               

              Rick - with your multi-file solution, are you able to go to a  layout from another file but make it look like you're not actually opening another file?  ie Can you do it all within the one window?

              • 4. Re: Two databases or keep growing the current one?
                RickWhitelaw
                  

                Hildy, 

                 

                You can create a new layout in File A based on any table (from any file) related to a table in File A.

                 

                RW 

                • 5. Re: Two databases or keep growing the current one?
                  user14360
                    

                  But what I mean is, say I'm going to create another file which is to house this other section.  Let's call it Projects.  If I go and build a whole lot of layouts in the Projects file which manages the projects (and yes, links into data in tables from the other file), then someone is in the other database file and wants to go to the Projects section, can that happen without it looking like they're opening a new file?  I mean, I presume you'd have to have two windows open and do some window manouevering.  But ideally I'd like it all to be able to happen within the same window and I don't want the user to actually even know that they've opened a new file.

                   

                  Sorry if I'm asking what seem to be silly questions! 

                  • 6. Re: Two databases or keep growing the current one?
                    philmodjunk
                      

                    It's not a silly question. That's what the data separation model is all about.

                     

                    You can create table occurrences that refer to tables in other files. Then you can create all your layouts in one file and there will be no window switching going on. You set your layouts to refer to the table occurrences and all works as though the tables were actually all in the one file.

                    • 7. Re: Two databases or keep growing the current one?
                      user14360
                        

                      Ah.  That's where I was getting stumped.  I was assuming that you'd also build the layouts in the other file, rather than all in the one file.

                       

                      If you build the layouts in the one file, what is the benefit of having the data in the other file?  Is it just for keeping the file size smaller?  Security implications in terms of limiting corruption issues to parts of a database?

                       

                      Because if you have all the layouts in the one file, that means you're still having to build all your relationships within that one file too, so you lose the benefit of reducing the complexity there on the relationship graph I guess.  But on the other hand, you don't have doubled up relationships in the different files.

                       

                      • 8. Re: Two databases or keep growing the current one?
                        philmodjunk
                          

                        You have a trade off to balance here. You could set things up with the classic data separation model: File 1 is the user interface section, all your scripts and layouts reside here. File 2 is your data section. All your data source tables are defined here. The advantage comes when you have to modify  a layout or script in your database. After updating the script and/or layout in file 1, simply replace the current copy with the new one and you're done. No data imports required as the data resides in file 2.

                         

                        You can also make your solution "modular". In this case, you can have simpler, modular relationship graphs and overall module design, but now you have layouts in more than one file and thus you have more than one window to manage in your solution.

                        • 9. Re: Two databases or keep growing the current one?
                          RickWhitelaw
                            

                          I'm coming to this after Phil's excellent replies. Although FM automatically creates layout "instances" for each table defined in a given file, there's no reason to create layouts in a given file unless you need them. When, from File A, you go to a layout defined in File A, but based on a table from File B, no . . . it's not the same as opening another file, and you needn't have more than one window active. Although I support with the Separation Model for its obvious benefits, I'm not referring to it specifically.

                           

                          RW