The foster/adopters are kept in a People Table. The birds are kept in a Bird Table.
Is it better to use a repeating field for fosters, and a repeating field for adopters? Or is it better to create separate fields for Foster 1, Foster 2, Foster 3, Adopter 1, Adopter 2, Adopter 3 ... fields.
Create a join table that combines foreign keys for birdID and PersonID, plus a role field ("foster" or "adopter").
You should create another table that holds this information.
There you hold all foreign keys.
foster, adopter, bird, and possibly more information about the specific event like date, description ...
The bird can be only at one place in time so whenever it changes location from foster to adopter you register the data there.
Your last record will always represent the current situation and you will have endless possibilities for the bird to change location. Having Foster 1,2,3... fields will mean you need to modify your database schema and layouts if there will be more than you ever planned for.
Any time you think its appropriate to name field with numerics i.e. field 1 field 2 field 3 then you should examine your data model.
Thanks for trying to help. Sorry, I can't find a definition for role field, or how to operationalize that.
Here are my current tables/relationships. The relinquish, foster and adopter fields are all in the Bird Table right now.
You would put that new table "between" People and Birds:
People --< BirdPeople >-- Birds
where People::personID = BirdPeople::personID, and BirdPeople::BirdID = Birds::BirdID
"Role" would simply be a text field in that new table that helps you describe the bird/people relationship of a given record.
Can adopter be also a foster? Theoretically it's possible. I don't think you need the type field to keep things simple.
You can define the model in the join table.
fosterID ( linked to people )
adopterID ( linked to people )
birdID ( linked to birds )
Otherwise you'd need to create two records
1. personID, type = foster , birdID
2. personID, type = adopter, bird ID
The list on the left hand side would be exactly that join table, where some data comes from that table itself, other from the "parent" tables (like bird sex, name and other attributes, and person name etc.).
What I described as role is here probably "Status".
Yes, fosters hopefully become adopters! But sometimes there are several fosters before there is an adopter. And then the bird comes back a few years later for a whole new cycle of foster/adoption. Like this image.
If you're looking to reproduce the look and feel of this ( it's not FileMaker we're looking at just yet ), then you'll need some hand on help. It will require some more advanced techniques I'm afraid.
You definitely need that join table so you can track changes to bird's location ( who the fosters were, and for current one use the last record created ).
Who's relinquish? Is it once in a lifetime of the bird?, or a foster or adopter can become a relinquisher later?
Yes, there is only one relinquisher. But almost always multiple fosters/adopters.
Yes, the current software that was designed just for us is quite elegant, and perhaps hard to replicate. Unfortunately it can't be maintained so we need to go off the shelf soon. But I am struggling with the best way in FM to manage the flow of fosters and adopters across many years for each bird. I don't want to import 1000's of records until it can properly track the data and generate the right reports.
I added the BirdPeople connection table as recommended. Thanks again to everyone!!
If it's possible to export the data table by table to Excel for example, I'd start with that and reimport them into a new solution. It will create tables with same field names. Same data and serial numbers.
This would make your life a lot easier when it comes to migration and you can reverse engineer the relationships.
Or have you taken this approach already?