What to do when a child record has multiple parent records of same table

[ehwalker]

I'm working on a database to store/organise data pertaining to Radio/GPS collared cheetahs (e.g. movements, kills they've made, etc.). Within the database, my main table is 'Released Cheetahs' which contains all the information about an individual cheetah and their local ID number, which is called 'AJU#'. AJU# is the field relating 'Released Cheetahs' to the other main tables within the database (Fix Data, Kills, Tracking Sessions, Parturitions, Releases, Captures, etc.).

Some of these cheetahs (and thus the records within 'Released Cheetahs') are in groups of a few individuals, which is delineated by 'Group Name'. My problem is that when I make a entry into one of the other tables, let's say 'Kills' (which contains info about the prey they have successfully hunted), with my current design I would need to duplicate this record for every cheetah within the group, but I would rather be able to just list all of the cheetahs (i.e. AJU#s) within a single field of the 'Kills' table so that there is only one entry per kill.

So, my question: What would be the best way of tying each kill record (single 'child' record) back to each of the cheetahs (multiple 'parent' records) it pertains to? I know I could just look at this from cheetah group rather than individual, but even though there is a group each kill record does not always pertain to each cheetah within a given group. I have tried playing around with repeating fields (e.g. using a repeating AJU# field within the Kills table) but I can't quite seem to get this to work as hoped. Any advice is most welcome!

I've attached an image of my relationship graph in case that helps.

[comment]

By your description, the relationship between Cheetahs and Kills is many-to-many. The "proper" way to resolve a many-to-many relationship is by using a third join table. However, Filemaker provides an alternative in the form of a multi-key field. This is very easy implement (all you need is a checkbox field in Kills to select the IDs of the cheetahs participating in the kill) - but has some limitations when producing detailed reports.

[ehwalker]

Hi, thanks for the info. Could you explain the 'proper' way to which you refer? If there is a better way of handling this, then I would definitely like to do it that way. 

Also, I have the multi-key field working fine but unfortunately have a couple of lookups/calculations that run off of that field. These lookups/calculations only return a value for the first value in the multi-key field but I need the lookup to run for every value in the multi-key field. Any advice on how to accomplish this? I've been scouring the forums but haven't really found anything helpful.

[LaRetta]

On November 7, 2015 at 10:58:45 AM, comment said:

By your description, the relationship between Cheetahs and Kills is many-to-many. The "proper" way to resolve a many-to-many relationship is by using a third join table. 

Hi Eli,

A join table would sit between Cheetahs and Kills.  It would hold the primary ID from Cheetahs AND the primary ID from Kills.  This resolves the many-to-many issue.  

Here is an example of join - just change Invoices to Cheetahs and change Products to Kills.

Join tables

The 'line items' table is what would be your new join table.  :-)

[comment]

8 hours ago, Eli Walker said:

I have the multi-key field working fine but unfortunately have a couple of lookups/calculations that run off of that field.

Can you explain in more detail the "lookups/calculations" thing?

[ehwalker]

In the Kills table (and most other tables outside of Released Cheetahs) the AJU# for a new record is a lookup based on the relationship to Released Cheetahs. So upon data entry, the AJU# is populated automatically based upon the cheetah name that is entered in the Name field (each cheetah has a unique AJU#). So, if I have multiple cheetah names within the Name field of a single record (by using a checkbox), how do I get the AJU#'s for each of those names to auto-enter/calculate within the same single record? 

Edited November 17, 2015 by Eli Walker

[ehwalker]

Well I think I understand the join table concept well now and I believe it will work for me really well (I'll just have to figure out how it will work when importing data). My question however is how do reports function across this sort of join? I've looked everywhere for information about reporting with join tables but I can't find anything... Any help on this?

[comment]

4 hours ago, Eli Walker said:

So, if I have multiple cheetah names within the Name field of a single record (by using a checkbox), how do I get the AJU#'s for each of those names to auto-enter/calculate within the same single record? 

I would use a value list that uses values from the AJU# field (in the Cheetahs table), also displaying the Name field. You can set this up so than only the Name field is shown. That way users think they are selecting names, but the field actually contains the IDs.

You should consider using the same for selecting the cheetah in the other child tables as well.

 

2 hours ago, Eli Walker said:

My question however is how do reports function across this sort of join?

Normally you would find the records you want to report upon (e.g. within some date range), sort them the way you want to report them and present them in a layout with sub-summary part/s. For example, sort the join records by the AJU# field, and use a summary field counting the records to display the number of kills for each cheetah.

Edited November 17, 2015 by comment

[ehwalker]

On November 17, 2015 at 1:36:15 PM, comment said:

I would use a value list that uses values from the AJU# field (in the Cheetahs table), also displaying the Name field. You can set this up so than only the Name field is shown. That way users think they are selecting names, but the field actually contains the IDs.

You should consider using the same for selecting the cheetah in the other child tables as well.

 

Okay, I've done this before so I know how it works. But are you suggesting that I don't use the Name field at all in any child tables but rather just the ID? If so I guess it makes sense, as long as Name is the value shown

 

On November 17, 2015 at 1:36:15 PM, comment said:

Normally you would find the records you want to report upon (e.g. within some date range), sort them the way you want to report them and present them in a layout with sub-summary part/s. For example, sort the join records by the AJU# field, and use a summary field counting the records to display the number of kills for each cheetah.

Okay, so just using the records within the join table to build reports? Rather than the table that has the actual data?

[comment]

1 hour ago, Eli Walker said:

But are you suggesting that I don't use the Name field at all in any child tables but rather just the ID?

Yes. This will allow you to change the name in one place only, without breaking existing links.

 

1 hour ago, Eli Walker said:

Okay, so just using the records within the join table to build reports? Rather than the table that has the actual data?

Yes. Although in many cases the join table will have some data too.

 

 

Edited November 20, 2015 by comment