I have been updating a database for an animal care facility and we have a peculiar problem in terms of historical records for some residents. The pigeons were originally identified by colored leg bands but the bands on the pigeons faded over time and are no longer identifiable to the original colors. The pigeons now have new bands with numbers put on, but there are five pigeons that cannot be definitively linked to the original colored-band records though we have been able to narrow it down to two or three old records for each one: e.g. pigeon 13 could have originally been pigeon “orange” or pigeon “red”. What I want to do is create new resident records using the number band identifiers so going forward we can enter data on the correct specific individuals, and somehow archive or link the color band records to the numbered individuals they might be. I’m having trouble conceptualizing how best to do this if it’s even possible. I’ve considered:
-making a new table of “archived” pigeon records and linking new records to that
-adding a field to the residents table to indicate archived records and just making a note in the new records to look at the old ones if needed
-making a pdf report of all the data from the potential historical records and attaching those to the new records with the appropriate caveats noted
I have attached a jpeg that shows the fields and relationships of the tables where animal information gets entered.
I'm wondering if I'm thinking about this wrong.
Thanks in advance for your thoughts!