8 Replies Latest reply on Jun 25, 2012 9:54 PM by godeak

    Relationships between tables and fields in multi-site, multi-project db?



      I am a relatively new user. I'm developing a db for four sites (laboratories) collaborating on a research project. Each site will collect data for a battery of tests. The results will be entered separately at each site, but will later be compared across sites, so the tables at each site must be combinable.

      We also need personnel records at each site -- also combinable, eg for email, calendars.

      Finally -- importantly -- the tests are behavioral tests given to children (to track language development). We need, at each site, a lot of information about each test participant (Child). Each child will do several tests every 6 months or so; call this a "round" of testing. So the hierarchy is:

      Site > Child > Round > Test1,2,3.

      We also have: Site > Personnel > Resources (e.g., Test materials,announcements)


      If you are still with me...I can't work out the best way to organize all this, using match fields, lookups, variable lists, or just layouts that draw upon several tables.

      Site is obviously a high-level global field. Child and personnel are also global resources to organize records in multiple tables (e.g., multiple tests; which test a given personnel has administered). I think Round of testing is also a global field, because there are several tests in each round of testing.


      And that's where I'm getting confused. There are lots of relationships to cross-reference. For example:

      - somewhere record *which* personnel enrolled a given child, or administered the tests in Round 1, 2, 3, etc.

      - track which child has completed which tests, and when (e.g., is the child due for the next round?).

      - check how many children have finished a certain Round of testing at each site.

      - generate table for children at certain Sites, or 1 site, or all sites, showing their results in Test X, in specific Rounds.

      And so on


      So: Should I use one Global Resource for each *Site*, one for each *Subject*, and one for each *Personnel*? What about *Round* of testing?

      One problem I'm having is that within each table -- say, the results of Test X -- I can't seem to make matches to fields form several tables. That is, I want a Text X table to refer to the correct child and the correct tester (personnel), but those are in different tables. If I try to match both field in, it creates a duplicate table for that Test. What are the ramifications of a duplicate table? Does it automatically self-refer to the original table? Or do I have to make it do so?


      Also, I want that Test X table linked to global Site and Round fields, which cut across sites, children, etc. But how? With a look-up table? Or another match relation?


      So my biggest problem now is, how do I match multiple fields in lower-level tables to global fields in several different resource tables? Is there some trick? Is it ok to use those duplicate tables to do that, and do the duplicate tables draw upon the same records (ie the same underlying data)?


      Any help would be really appreciated! BTW, using FM Pro 11 (I plan to upgrade to Advanced tomorrow). Thanks!

        • 1. Re: Relationships between tables and fields in multi-site, multi-project db?

          What I'm missing in your description is how you are going to "link" the data: are you envisioning each site running their own copy of the solution on their own FMS, with file references in each copy to the files running on the other sites?


          That would be very fragile and a feature/bug fix update maintenance nightmare.

          How about running the solution off one central FMS and each site connecting to that one?  That should make the table structure a whole lot easier.

          • 2. Re: Relationships between tables and fields in multi-site, multi-project db?

            Thank you for that questio/suggestion. That question was exactly one of the reasons the posted question has been driving me crazy -- I've never run a database server before. Actually I've been thinking of exactly your solution: Setting up a server in my lab, and having everyone else enter & query off my server. Sounds like I was on the right track?


            If that's what I do, then that would support the hierarchy I outlined, right? My tentative plan:

            - Globals for each site

            - A common portal GUI from which personnel would "enter" their site's tables/layouts (limted by permissions & log-ins)

            - Special layouts designed for the principle investigators to do certain data analyses across sites; these would just grab combinations of fields (copied or clculated) from each site's tables, and put them in a single table.


            Does that sound like a plan?


            Another question: If I do set up a server, should I (1) Put it on a dedicated machine? (2) Upgrade to FM Pro Server? Maybe total of 5 licenses & 16 users (20 max, but never more than 2-3 at a time) across  sites. Do we need the Server upgrade for that? (However, this is a less pressing question than the problem of relationships across tables & global fields.)


            Thanks very much!

            • 3. Re: Relationships between tables and fields in multi-site, multi-project db?

              If you have everything on one server you don't need separate tables for each site.  Instead you would identify the records as belonging to a site.  That will make reporting and totalling, any kind of reporting really a whole lot easier.  Also saves you from having to make layouts that are site specific.


              Also the concept of "globals" have a special meaning in FM: they are values that are unique to the user's session, you can't use them for site-specific data that would persist across sessions.  Any site-specific preferences should be stored in a site record.


              FMS: yes, dedicated machine.  You definitely need FileMaker Server.  While you can host with FM Pro it is very limited, fragile and does not allow you to do live backups.  You do not want to be without regular backups.

              1 of 1 people found this helpful
              • 4. Re: Relationships between tables and fields in multi-site, multi-project db?

                (ok, still learning terminology. Thanks for clarification re: global)


                So,  keep all records from all sites in 1 table, linked to a single layout. But can I make conditionals so most users interact w/  only their own site's data? (Each user will have a unique log-in.)


                For example, could I make a tabbed layout, with a tab for each site, and limit users to only  certain tabs?


                I'm still confused about having relationships (match fields) from fields in two or more different tables, to fields in a single related table. Is there a trick to do that? Or, when that duplicate related table is created, is it a sort of "mirror" table that has all the same records?


                Thanks for advice re: FM Server. Any thoughts WRT 11 vs. 12? We all have 11, and limited funding. Is 12 so much better, and if so, can a server run 12 while clients run 11?

                • 5. Re: Relationships between tables and fields in multi-site, multi-project db?

                  Hi godeak,


                  I suspect you will find that setting up different Privilege Sets for users at each of the separate sites will give you the simplest way of controlling access and then the viewing of entered data.


                  There will presumably be some supervisors who will need a Privilege status that enables them to view results from all sites. Once you sort out who gets to enter what data and then who gets to see that data you will have a Privilege Set hierarchy that will help you to set the necessary access rights for each user.


                  I don't believe you necessarily need V12 unless there are some impediments to the WAN performance between any site and your FMS location. Pinging the separate IP addresses will give you some preliminary indications of the latency. V12 will certainly give you better WAN performance but you may not need it, especially if you keep your main tables to a modest number of fields.


                  You cannot use FMP 11 clients to access files served by FMS 12. The file format is different for V12 and not interchangeable between these two versions. If you have V11, you can get the development started in V11 then move to V12 when it is deemed necessary or advantageous.


                  I hope this helps a liitle bit as I imagine that you still have some unanswered questions.





                  1 of 1 people found this helpful
                  • 6. Re: Relationships between tables and fields in multi-site, multi-project db?

                    Thanks John. I'm going to look into a server & upgrade to FM Server.


                    Is it pretty smooth hosting from a server in one secure network (https) to clients alldifferent secure networks?


                    Using a server, we have to make sure personal data are  secure. Better to use a VPN? Or, is it possible for each site to keep personal info (name address etc) on local dbs? Can specific fields be assigned as "local only, not  saved to a server? Each site has its own license, and could set up their own backups.


                    Thanks again -


                    • 7. Re: Relationships between tables and fields in multi-site, multi-project db?

                      I rcpmmend you use a Filemaker hosting service to host your data starting out. It saves cost and they can give you support and they are the ones who com in on weeknds if the server stops running for any reason. They aslo know how to set up ecrypted data trasfer and will answer all your questions setting up.


                      I STRONLY RECOMMEND reading the Wikipedia article on Normal Forms for data stuctures and follow the guidlines for 4th normal form as far as possible.



                      • 8. Re: Relationships between tables and fields in multi-site, multi-project db?

                        The wikipedia aritcle is really helpful, thanks.


                        I will look into hosting services.




                        Gedeon (btw, I'm also Oliver - middle name tho)