I have been working for several months on creating a database from scratch for tracking a small animal care program. I am struggling with the relational structure as follows:
We serve several different facilities, each of which have animals. All the animals are related to the facilities table through a foreign key and my forms subset the animals the user can choose to enter information on based on choice of facility in a combo box.
An animal can be:
-part of a group (sometimes treated as an individual and sometimes as a group, e.g. an individual dove in a pen breaks a leg vs. a all the doves may be exposed to an infection and get the same dose of an antibiotic)
-not part of a group (never belongs to or is treated as part of a group)
Sometimes an animal is initially treated only as an individual and we later find out it is part of a group.
I am having trouble designing a structure that will accommodate these situations for relatively simple user-entry of data. My current solution (and there have been several that have proven to be ill-conceived) is to have a parent table of facilities and a child table of animals that can also belong to a group: the groups are a separate table related to the animal table. I am planning on writing some code that will allow a user to press a button to batch-apply things like exams or updates to multiple animals using that group table. I will also have entries in the animal table that are "cohorts" of animals that only ever get treated as a group (tank of fish).
Is there a better way to do this? Am I thinking about this the wrong way?
Thank you for any help!