AnsweredAssumed Answered

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

Question asked by godeak on Jun 24, 2012
Latest reply on Jun 25, 2012 by godeak


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!