Circular Relationship Problem

[teganthomas]

I am not an experienced user so I may be way off track with this but any help appreciated.

 

I am building a database to manage Dog Breeding data and I have hit a problem that I am struggling to resolve.

 

The Database has a “Dogs_Details” table that holds the relevant details specific to each animal (appox. 4000 records spanning 150 years, many historic records are incomplete but that is not a problem).

There are various tables linked to the “Dogs_Details” table that hold other relevant data about health, parentage, (i.e Pedigree), registrations, identification etc.

The “Dogs_Details” table has a join table with the “Owner_Details” table so Owners and Dogs are linked.  So far so good - it all works.

 

I am now working on the breeding part of the database so there is a “Season” table to record Bitches receptive periods, a “Mating” table that records matings, a “Whelping” table that records details of the birthing of each litter of pups, a “Litter” table that records the progress of each litter of pups and a “Whelps” table with a record for each puppy's individual progress in the Litter.  Everything seems to work fine although I am sure my relationships are untidy to the expert eye.

 

However.

Once the puppies have reached a point of maturity where they may be homed they need to be added to the “Dogs_Details” table as Dogs because they have become dogs in their own right, so how do I link the whelp to its adult record? 

 

To be precise the part I am struggling with is;

How do I get the Whelps (puppies) recorded in the “Whelps” table to become or link to their adult “Dog” record in the “Dogs_Details” table.  I have tried adding a field with the UIN (Unique Identifying Number) of the Whelp to the Dog's record in the "Dogs_Details” table as a FK and I have also  tried a join table with the Dog's Unique Identifying Number and the Whelps Unique Identifying Number linked but whatever I do I finish up with a circular reference that isn’t permitted.  I am at a loss.

 

The analogy might be with other animals such as (Human’s) where individuals produce offspring who might go on to produce offspring themselves - how is that handled in say a medical database.

 

It is not practical to add all neonates to the "Dog's Details" table because not all may survive infancy and that would lead to numerous pointless records but I do want to track the progress of say a runty puppy through to adulthood and track such things as longevity and health.

 

I don’t have a background in Dbs and although the database is now doing most of what I need I can’t fathom out how to resolve this conundrum.

 

Is it even possible? or do I have to just work around it?

 

I have attached an image of a relevant section of the ERD in the hope it explains things a little better.

 

TIA

[comment]

You need to place another table occurrence of the “Dogs_Details” table (why not call it simply "Dogs"?) on the relationship graph (which BTW is not an ERD). Then use this TO to define a relationship with Whelps, using WhelpID as the match.

[teganthomas]

Thank you for the help.

 

I am still struggling to get my mind round this - how does a an additional TO work?  I used multiple TOs to create the Pedigree Layout and that seemed to make sense to me but I can't grasp the logic with the whelps becoming dogs scenario.

 

I have created a new TO of "Dogs_Detais" but with the Pedigree relationships I already had a Sire and a Dam field in the table.  Would I create a new field "UIN_WhelpFK" in the "Dogs_Details" table and this would then be the field that I would use as a match for "UIN_Whelp" ? 

 

As you can tell, I am a novice!

[eos]

Would I create a new field "UIN_WhelpFK" in the "Dogs_Details" table and this would then be the field that I would use as a match for "UIN_Whelp" ? 

 

That's correct – you would add a Whelp foreign key ID to the Dogs table (I second comment: why “Dogs_Details”? Is there a “Dogs_summary” table somewhere?)

 

If you “promote” a whelp to a full-blown dog, you'd create a new Dogs record, and add the primary key from the Whelps table as the foreign Whelps key in the Dogs table.

 

Now you know that this Dogs has a Whelp record in your file.

 

But to implement the desired relationship (i.e. access the Whelps record that this foreign key references), you must add another TO of the Whelps table (let's call “Whelps_direct”, but you can choose any unique name) and match it via Dogs::whelpFK = Whelps_direct::whelpPK.

 

The reason for adding another TO becomes clear when you consider that you already have an (albeit indirect) relationship between Dogs and Whelps. If you (could) add another relationship between the existing Dogs and Whelps TOs (now a direct one), FileMaker wouldn't know what – from the context of Dogs – the reference Whelps::DOB means. Get the date of birth, but following which path?

 

That is why you cannot create circular relationships (and where the FileMaker RG is different from an ERD).

 

With the new TO, you would reference the DOB field as Whelps_direct::DOB (or e.g. show the DOB field via this TO on your Dogs layout), and the meaning would be unambiguous.

[teganthomas]

Thank you so much both of you - very much appreciated.  I had used TOs to solve constructing a 5 gen pedigree but I that was by rote - I really didn't understand TOs at all, now I have some idea and I can build on that - a bit like - "give someone a fish - they are fed for a day - teach them to fish and they are fed for life."

 

Problem solved - thanks again.

 

Reason the table is Dogs_Details not just Dogs is historic - it was called that in the ghastly old non relational widows database I have used since 2005 so the table was imported with that name and I really don't have the confidence to tinker under the bonnet and start changing things - maybe in time!

There are several 'Dogs" tables (Dogs Eye Health, Dogs Joint Health, etc.) may change it later on if I get really confident (not for a while).  But I take your point onboard.

 

[comment]

I had used TOs to solve constructing a 5 gen pedigree

 

Perhaps you should take a look at:

http://fmforums.com/forum/topic/62995-directory-calculation/?p=298035

 

Another one that might be interesting:

http://fmforums.com/forum/topic/41352-how-do-i-create-new-record-if-certain-fields-are-filled-in/?p=192957