My Project:

I have employees who are required to go through a checklist when they encounter a duck, a cat, and a human. In our business, we have a set number of ducks, cats, and humans that come in and out of our doors. When they come in, we have to check that all of these features are present.

The features:

Here is our current database design:

Current Approach:

1. When an animal enters our building, we get their ID.
2. Their ID gives us their animal record.
3. We create a new “check_in” record for the animal
4. Under the check_in record, we use the template given by the animal_type->feature_type relationship
   • For example: Sam the duck will have a new check_in created with separate feathers, wings, beak, feet, and eyes check_in_feature entries.
5. We go through the process and set feature_is_present to true or false for each of the features
6. When done, we check them out and the check_in entry is complete

My Question:

In practice, this relational schema is very hard to work with. We’ve used it for years and are constantly slowed down when looking up information because of the weird joins this design tends to require. I have no doubt that this is a known problem with a more elegant solution than using this naive approach. Anyone know a better way to structure this, or better approach that will be easier and more efficient to work with?

EDIT:
Just realized there are artifacts from our actual system in the navigation properties sections, just ignore those. I don’t want to recreate the images if I can avoid it