Data relations among three tables, repeating info

[Tony Morosco]

Hi folks,

A sticky three table data/relationship issue. I am trying to figure out how to establish a relationship between two tables that, by logic, are two table relationships apart, hopefully without duplicating data in multiple tables. I would appreciate people insight on my data structure and if there is a way to accomplish what I want to do.

I've always seen people ask for the real world issue rather than an abstraction, so here goes:

I manage a database for a Botanical Garden that tracks their plant collections. The database tracks, among other things, about 23,000+ accessions of 14,000+ species, in 18,000+ locations of hundreds of thousands of individual plants. There are quite a few tables and hundreds of fields, but I'll only mention the relevant ones unless a question comes up.

The three tables involved in this issue are:

Accessions, Species, and Locations (highlighted in orange in the attached graphic).

Accessions - A table of individual accessions. An Accession is defined as 1 to hundreds of individual plants, all of the same identity (species name), all received from the same source on the same date. Each new shipment of plants gets a new accession number, regardless if the gardens already has that species under another accession number or not. Each individual plant can be placed in a location, so an accession of many plants can be split up to multiple locations. One record per accession.

Accession Number - unique key field, can not modify

Species ID - identifies species name for this accession.

Species

- A table of information on botanical species and their characteristics (scientific name, common name, flower color, height, native region, etc.) One record per species name.

Species ID - unique key field, running serial number, can not modify.

Taxon - scientific name (actually a calculation from the individual components of the name)

Locations

- A table of information on the individual locations of accessions. Each accession can have multiple individuals, and each individual can be in one or more location. But all individuals should have the same Species ID. One record per unique Bed number - accession number combo (a superkey).

Location ID - unique key field, running serial number, can not modify.

Bed Number - bed location in garden

Accession Number - Accession number for the plants in this location.

The relationships are currently set as:

Accessions is related to Species by matching Species ID in both tables.

Accessions is related to locations by matching Accession Numbers in both tables.

There is no direct connection between locations and Species, because there is no logical direct connection. There is a logical relationship of course, in that each specimen location has an accession number, and each accession number has a species ID, but since the species table is two tables away from the location table, I can't relate Species to Locations directly.

Examples of why I need to have a direct relationship to do what I want to do are:

* From the species table, I want to display a portal list of location records for each species name.

* On each location record, I want to display if this location is a unique location of this species, or if there are other locations of this species elsewhere in the garden (this can be done by counting a self-related match on locations::species ID) This helps the gardeners evaluate if they should think twice about removing a plant that may be the last one left at the institution.

Possible solutions that have run through my head-

The obvious thing to me is to get the Species ID value into the locations table (locations::Species ID). This is how I had the database set up in the beginning.

The problem with this is, the species ID of an accession is not inviolate (it can change) It is not uncommon for plant identifications to change based on expert's review or further information. So if the Curator updates the species ID in the accessions table, all the Species ID in the locations table need to be corrected as well! A data management nightmare if you have just ten accessions to update, and each of those has ten locations- 100 record update. It just seems so wrong for a relational database!

So I of course tried making locations::Species ID a calculated field that is equal to Accessions::Species ID, but I can't establish a relationship between tables based on that.

If I make Locations::Species ID a numerical field populated by lookup upon creation, I could run a nightly script via FileMaker Server to synchronize it, or experiment with FM 10's script triggering, trying to set up a script to update locations:Species ID when Accessions::Species ID is modified.

I actually have this problem with my Images table as well, in that each image is of an accession number, which also has a species ID, but if the species ID changes for the Accession, it should change for the image as well.

But somehow I keep thinking there is a way to get the locations and species related in a way that will work, and that I am missing something...

Any thoughts would be appreciated. I can set up a guest login to the database if you are curious.

[Ocean West]

here are a few methods that come to mind....

http://www.filemakermagazine.com/videos/infinite-hierarchies-creating-a-folder-tree.html

http://www.filemakermagazine.com/videos/infinite-assignments-never-ending-value-lists.html

[comment]

Species -< Accessions -< Locations >- Beds

From the species table, I want to display a portal list of location records for each species name.

Just go ahead and do it - it will work with your current structure.

On each location record, I want to display if this location is a unique location of this species, or if there are other locations of this species elsewhere in the garden (this can be done by counting a self-related match on locations::species ID) This helps the gardeners evaluate if they should think twice about removing a plant that may be the last one left at the institution.

There are several ways to do this. The most straightforward one would be to script this and display it "on demand" only.

If you want this "live" and accessible just by browsing location records, you could add two TO's to the relationships graph, so that:

[color:red]Locations 2 >- Accessions 2 >- Species -< Accessions -< Locations ...

Now just count, from the context of Locations, the related records in Locations 2. A count of 1 means a unique location for this species (the record includes itself in the count).

(Note that Acessions 2 could just as well be linked to Accessions directly as a self-join, by a common SpeciesID value.)

[Tony Morosco]

Ocean West,

Perhaps I am missing something, but I don't see how a paid subscription to view "Infinite Assignments - Never Ending Value Lists" applies to doing a two table hop through relationships. I am guessing this is a self-relationship solution, like the kind I have set up for some botanical name files?

I did track down my problem, and it was with an erroneous relationship that I will explain in my next reply.

Thank you anyways, I always appreciate time volunteered.

[Ocean West]

Tony, I was suggesting the site http://www.filemakermagazine.com as a resource for your continued education, yes I realize it is a subscription based offering but the the quality of the techniques offered are well worth it for any developer. These two techniques are someone relevant to your initial request. Every few articles is freebie episode.

cheers.

BTW I am glad you isolated the conundrum .

[Tony Morosco]

comment-

Thank you for the reply!

My first reaction was "What?? What do you mean it will work with my current setup?? It isn't working!" But that was what I needed to get me thinking along the right track.

The key piece of information that helped me solve the problem was of course something that I left out of the posting. My solution was first programmed in FileMaker 5, with the one table/one file solution. It has been upgraded over the years but has been badly in need of an overhaul to bring it into a one file/many tables solution. In fact I just started doing that last week, as a volunteer (sigh, no paycheck for this one).

But getting back to the problem- The problem was that the relationships are set up independently in each file in one table/one file solutions, and in the Species file, the relationships were set up differently than the graph I posted, Species was directly related to both the locations and accessions table. (unlike the relationships in the accessions table, which were as posted in the graph). Thus I couldn't set up a proper table portal and nothing was working.

*sigh* So it is my bone-head fault for missing that and I apologize for using so much bandwidth, but am grateful for pointing out the obvious. I'm not sure how I convinced myself that a direct relationship between the two tables was necessary. I will be *GLAD* when I've got the whole mess in one file and can manage it more effectively. I wish it had happened years ago, but that is the way it goes with lean non-profits.

And thanks for the innovative way of denoting unique specimen locations. Quite nifty!

Many, many thanks!

I've sometimes wondered why FileMaker didn't choose to show external table relationships different from tables that are internal to the file at hand (different color or something).

[Søren Dyhr]

I've sometimes wondered why FileMaker didn't choose to show external table relationships different from tables that are internal to the file at hand (different color or something).

You mean that italics are too weak a signal? But coloring wouldn't it collide with the developers choises of colors?

--sd