Optimize Schema for JOIN across large but finite group of tables

I have some flexibility here so I'm looking for some advice before I lock things down. I also have a couple ways of solving this problem but I'm looking for advise on the most efficient way of doing this. Since the specifics of my data types are a bit obscure I'll use a more understandable object metaphor.

Right now I have two main tables, and a large but finite number of additional tables. The following business logic applies.

1. Each specific animal table has a unique filed assigned to it, something like "snout diameter" for a pig, or "whiskers" for a cat. There is also another field
2. The animal table has a field marking the animals "Role".
3. There can be multiple animals in a cage.

4. Animals are linked to Cages by FK constraints. The specific Animal tables are linked to the animal table by FK constraints.

   • Cages
     • Animal
       • Cat
       • dog
       • Pig, etc

The main question being asked is, what's in a cage? I also need be able to search through all the cages as quickly as possible and get all the info for animals that fall into the role "tasty". Sometimes开发者_StackOverflow a pig will be "tasty", other times it could be a cat. Depending on the type of animal that's "tasty" I need to display it's specific info.

What's the most efficient schema design, or SQL statement to find this info?

My first attempt at this had Only Cages and then a bunch of "SpecificAnimal" tables. This seemed like a bad idea because I would have to do a join across 10+ tables to figure out what was in a cage. I then moved common attributes to the Animal Table, this allowed me to easily see what animals were in a cage, although this still required searching across the specific tables to get all the data. I contemplated storing the specific attributes into some form of CSV string (but I'm not that desperate yet) Of course I could go EAV, but that also seems inefficient since there really are a finite number of animals.

Am I being to worried? Should I just bite the bullet and accept Joins across 10 tables? Just worried about performance.... Any ideas, or design patterns that can be recommended. Suffering from information overload and a head cold. Help please.

---

It's really hard to answer 'what is the best schema' questions, because they always involve tradeoffs. Part of that means that to accurately trade off one design against another, you have to have measurements (of speed, for example) to base your decision on. (This is probably not the answer you were looking for).

For what it's worth, 10 joins is not a massive number, and depending on the number of animals and cages in your system you might never notice a speed issue. Further, if there really is one 'main query', then you can use materialised views to make at least that query fast to answer.

Finally, some overarching advice: go for a clean data model until you have hard numbers to dictate that you 'muddy' the design.