Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
1  
You might want to ask the moderators to migrate this to dba.stackexchange.com/questions –  FrustratedWithFormsDesigner Feb 8 '13 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 '13 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. –  Jimmy Hoffa Feb 8 '13 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 '13 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. –  MichaelT Feb 8 '13 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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