6 Replies Latest reply on Sep 27, 2012 8:47 AM by DrewTenenholz

    Data Structure


      I need some advice. I have a product which consists of around 80 tables all in one file. I am looking to move to the data separation model but within this i am wondering if i should have a single data file with all the 80 tables in one file or split them out into separate data files eg so each table will have its own file like it was back in FMP 5. Maybe not have all tables as their own file but maybe group certain tables together in one file.


      The reason i ask this .........is if all tables are in one file and thereis a problem with the file i would have to recreate all tables to make a new file where as if they were split into separate data files the work would be less and would be easier to replace certain files of the solution. Are there any other disadvatages in spliting your data files out into separate data files? does it affect performance?


      Many Thanks


        • 1. Re: Data Structure



          I have a large system that used to be all in a single file since then I have moved to a seperation model with 1 interface file and many data files. Each data file has 10 - 40 tables. I have not noticed any major speed differences between the two models, but it has made updates significatly easier.



          • 2. Re: Data Structure

            Ray Cologon has a good discussion about the pros and cons of data separation in the FileMaker Pro Bible. I suggest you read this and anything else you can locate on the issue, then think long and hard before making your decision.

            • 3. Re: Data Structure

              I have one system where the original developer split the majority of the data tables out from one data table that does a mass import at least once a day, due to having a lot of indexing and corruption issues. Once I helped him set up the import as an update-append (i.e., update matching, add non-matching), the indexing and corruption issues went away. (And this was a while ago.)


              All other systems I have put all the data tables in a single file. I've not seen any issues with doing it that way. I think, from a maintenance standpoint, it's preferable not to have multiple data files, since you have a lot of stuff that has to be duplicated:


              - Relationships graph

              - Scripting

              - Accounts

              - etc.


              Further, you can run into issues with hosting space on your server. If you have 80 files in your system, that eats up about 2/3 of a server's available file quota. Doesn't leave room for much else.


              So, to answer your question, there's not much of a driver to split data tables out between multiple files, and pretty compelling reasons to merge them. At least in my experience.





              • 4. Re: Data Structure

                I do agree with Mike that when possible I too prefer a single data file. My multiple data file situation is unique as it is really multiple databases ( customer service, engineering, receiving...) with a requirement to have a single interface file. In this situation it just made sense to have each segment with its own data file. Accounts is the biggest pain since Filemaker doesn't have a built in way to move accounts file to file meaning its my job to make these tools or do it all manually.



                • 5. Re: Data Structure

                  Well it depends. I splited a 11GB data file into 6 smaller ones. The performance is significantly imporved. It is way faster than before especially on iPhone and iPad.

                  • 6. Re: Data Structure

                    Kev --


                    I tool a multi-file .fp5 system and rearranged it into several data files with one interface file.  I made my decisions based on grouping some tables together that had very few changes (accounts, preferences, country list, state list, value list table), and leaving others with a lot of changes - and thus more opportunity for corruption - separate from each other.  I like to use the example of a library system that might have one file for the items owned, and one file for the borrower records.  You can easily swap in an empty clone of the borrower records file once it is getting unwieldy or it is simply unnecessary to keep track that person x borrowed book y 7 years ago, especially if the book was sold last year at the book fair, and isn't even in the library anymore.


                    You can get the benefits of being able to update an interface file without touching the data, and depending on future needs, you could set up a 'localization' table in its own file so that you can easily have an on-site/off-site(offline)/multiple-site versions that can be integrated later on.  If this is even a far-off possibility, read up on SyncDek and other methods of synchronizing multiple FileMaker systems to help you prepare for that now.  (n.b. start using the uID function for every record in every table).


                    And Nick is right, if you go this route, you need to set up your own method of creating an updating accounts in each file with the appropriate script steps and a place for an 'admin' to add/remove people from the system.  That said, if you can do it for two files, you can arrange it to work for more than two files.


                    -- Drew Tenenholz