Jump to content

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


ehwalker

This topic is 3023 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Screen Shot 2015-11-07 at 5.16.45 PM.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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.  :-)

  • Like 1
Link to comment
Share on other sites

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 by Eli Walker
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

This topic is 3023 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.