Multiple intersection tables vs multiple joins

Question

I have a hierarchical relationship between my tables, with the children having foreign keys referring back to their parent ids (assuming id is the primary key for each table):

Department has many Category Groups
Category Group has many Category(-ies)
Category has many Sub-Category(-ies)
Sub-Category has many Attributes.

Now, all these entities except for Attributes are optional meaning if I don't select anything on my hierarchical cascading dropdown based UI, I need to display the Attributes that belong to all Departments, if I only select a Department then I need to display Attributes that belong to all Category Groups belonging to that Department and so on.

Obviously, one option to implement it is to do a inner join between all the tables to get to Attribute. For instance, if nothing is selected it will be:
Department inner join Category Group
inner join Category
inner join Sub-Category
inner join Attribute
to show all the attributes belonging to all departments.

The other thought in my head is to have intersection/relation mapping table(s) -
DepartmentAttributeRelation which has foreign keys to Department and Attribute,
CategoryGroupAttributeRelation which has foreign keys to CategoryGroup and Attribute and so on.
This will enable direct search to get to the Attributes given any entity.

My question is - Are there any downsides to the second approach above or are there any better approaches to solve this?

Answer 1

What's the problem with the inner join? Create a view so you don't ever have to see or write the ugly multi-join query again.

The alternative's first and more important drawback is that you can have inconsistencies i.e. you can have data in those "join tables" that could potentially contradict themselves, i.e. insertion anomalies are possible. You would need to write code to prevent those anomalies from occurring.

Keep in mind, though, that sometimes when one believes a hierarchy will forever be at much, say, 4 levels deep, then a requirement comes around when a sub-sub-category is needed and the fixed level hierarchy design breaks. A future-proof solution where there can be an unknown depth of levels of hierarchy and only the "leaf" elements can have attributes is a matter of another question and another answer.