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: example

Here is our current database design: enter image description here

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

  • 1
    You might want to ask the moderators to migrate this to dba.stackexchange.com/questions Feb 8, 2013 at 16:46
  • 1
    I disagree, I'm not administering a database here. I'm designing the program and coming up with a more effective approach to my data structure. I think programmers can help me much more than database admins when deciding what data structure to go with. The fact that my data structure is persistent and might end up on a technology that they are familiar with seems irrelevant.
    – justausr
    Feb 8, 2013 at 17:17
  • I would have a close read over here programmers.stackexchange.com/questions/180758/design-parts-db I think you're looking at an identical relational-schema and the approaches mentioned would apply completely. Though you'll note I don't agree with the answer chosen over there, I think you currently are using option #1 mentioned in that Q and I would suggest to you it's time to go to option #3 as you'll get more uniformity which is what you want I think. Feb 8, 2013 at 17:22
  • A key difference is that I have the check_in_feature table. In terms of the linked question, my duck, cat, and human, would be wires, contacts, etc. Where I differ is that I have the check_in_feature table which is an extra dimension on top of their problem. I have multiple instances of the same animal as opposed to single instances of different parts. This is where my problem comes in. The animal features need to be all treated as a whole under the check_in, to do that there are painful joins. I'm using the EAV approach they mentioned, but with my extra dimension, it doesn't work as well.
    – justausr
    Feb 8, 2013 at 17:33
  • 2
    DBA.SE has more than just administration. There is a fairly active tag of database-design which appears to have questions similar to the one your are asking.
    – user40980
    Feb 8, 2013 at 17:35

1 Answer 1


If the mere fact that you have multiple joins are slowing down your queries, then either your db needs tuning or you need to move to a better RDBMS that can handle your joins better. I don't see anything wrong with your schema.

Assuming your DB supports this, my suggestion is to look carefully at query plans for your queries and look for cases where new indexes may solve your problems. This feature is extremely db-dependent.

Finally something missing from the discussion is reporting needs. This is something which can and frequently does rule out any non-relational solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.