3 Replies Latest reply on May 7, 2010 11:36 AM by comment_1

    Combine three databases or try to link them with an external common table?



      Combine three databases or try to link them with an external common table?


      I have 3 FMP 8.5 databases, which were created from the same database way back when to make things easy to keep track of separately.  I'm now being asked to put them back together, and while they are all pretty simple (no relationships, one table), they are each used for different purposes with a core of similar fields but each now evolved to having unique fields, scripts and layouts.  My intuition is to link them back using a table with the core of matching fields, but my NOT FMP trained boss is insistent that I have just one database so these core fields can be dumped in and out of a web site he is designing that will allow users to register lots of data, and view and update parts of their registered data.  I can do what he asks, but it seems more complicated than necessary and that all this exporting and importing of key fields of all records will have downsides to a system that works quite well currently.


      Would it seem to make sense to link these files through a table? If so, where can I find out how to do this?




        • 1. Re: Combine three databases or try to link them with an external common table?

          Certain things are easier to manage with a single file (security, scripting, and yes, web integration). Your instinct is probably correct that you don't want everything in a single table, though.


          For example, if you had separate files for students, teachers, and zombies, you'd put the common "people" attributes into one table, and then use related tables to store info that only applies to that type of person (e.g. classes enrolled, classes taught, brains eaten).


          You just need to make your boss understand that it will take (hrs he thinks it will take + hrs you think it will take) * 3 to get it done. Layouts are the most labor-intensive, since the parts have to be created manually; but calculations and scripts, even though they can be copied and pasted, still have to be checked for errors, etc.


          Since your existing files have no relationships, you can easily re-serialize the "common" table after importing all the data, and then you'll have a primary key to use to relate to your other tables.

          • 2. Re: Combine three databases or try to link them with an external common table?

            Your instincts are correct. The solution to this kind of problem is a central table, with 1-to-1 links to "satellite" tables. The core fields, and the primary ID exist in the central table, while the unique fields, and a matching ID (as a foreign key) exist in the satellite tables. 


            Every record of the satellite tables has a single matching record in the central table, linked via the central table's auto-enter primary serial ID, which exists in both tables.* Operations which require each of the satellites' records to be seen as the "same entity" use the central table's fields. Those which use only one of the satellites' records can use their table occurrences instead. 


            The strength of this system is flexibility. Each of the satellite tables can appear to the users on its own. A List view of a satellite table will show only its own records. Finds, viewing, etc. are separate from the others. That is the difference between this method and just using self-relationships for functional separation. These are really separate tables, when desired. Or the same when desired.


            It is all based on the fact that the central table and satellites have a 1-to-1 relationship. The method which ensures this is a little known ability of FileMaker to populate the keys on both sides of a relationship during the same operation, using the [x] Allow creation of related records. 


            You enter data for the central table, like the name, from a layout of one of the satellites. The relationship is based on the unique primary auto-entered key of the central table. A record is created, the central ID is populated. The same value is populated on the satellite side as well; the 1-to-1 relationship is established.


            I dubbed this the "David Graham" structure, after its creator. It could also be thought of as "separation of entities" (also my idea :-). If you do a search, at fmforums web site, for "David Graham", with me "Fenton" as the poster, you'll find a few threads.


            This is an example file I made. It may be a bit tough to follow if you're not familiar with relationships.



            • 3. Re: Combine three databases or try to link them with an external common table?

              I believe the correct term for this data model is supertype-subtype relationship - see, for example:


              With respect to Mr. Graham, I don't think he "created" it - it's been around, in one form or another, since the 60's.


              See also: