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?

• Tulains Córdova: Could you provide ER diagrams, however crude?
• linuxNoob: Do you happen to know an easy way to generate those?

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.

• linuxNoob: My main concern with inner join is that will it be slow when I make a REST call and do this as opposed to a direct look up? Would it be possible to give a simple example of the inconsistency? I am assuming this will only be specific to the join table? For the last paragraph - what are your thoughts on having a single Category table with a parent id column referencing itself to characterize the hierarchy and the Attribute table always refers to the leaf id?
• Tulains Córdova: @linuxNoob Don't think beforehand that it will be slow. If PKs and FKs are in place the RDBMS will do the job is was designed for, unless you're talking an inordinate number of rows when the problem begins to be a job for a noSQL database.
• Tulains Córdova: @linuxNoob An example of inconsistency is that you can insert into the CategoryGroupAttributeRelation table a relationship that is not present in the DepartmentAttributeRelation so lookups at different levels would have contradictory results. In a perfect world not you're app not you will insert that, but errors happens and the database will not complain. In the first design that will not be possible because there's a single route to every attribute.
• linuxNoob: Do you mind sharing some insights on the extensibility aspect as well? (the last paragraph of your answer)? Like how to go about designing that?
• Tulains Córdova: @linuxNoob About the extensibility, it would be god to ask in a separate question because it wanders off the original problem, also many people could benefit from that question (if it's not been already asked and answered).