Extensible hierarchical database design – Software Engineering Stack Exchange


This question is an extension to the question here (and follows the same hierarchy (1)) – Multiple intersection tables vs multiple joins based on this answer – https://softwareengineering.stackexchange.com/a/415095/278602 .

I am now thinking of designing an extensible hierarchical database. Here’s one approach I had in mind, have a single Category table with the following columns :
id (Primary Key)
parent_id (Foreign Key referring the id above)
name
dept_id (Foreign Key to the id on the Department table)
is_category_group
is_category
is_sub_category

I am thinking when the is_category_group is true then the parent_id can be null. Also, something I missed in my original question was that I’ll need a many-many relationship between Sub-Category and Attribute so I thought of introducing a join table, CategoryAttributeRelation with these columns:

category_id (Foreign key to id on Category table, will be sub-category/leaf id)
attribute_id (Foreign key to id on Attribute table).

Here’s what will be there on the Attribute table:
id (Primary Key)
name.

Most of the Attributes are global in nature i.e. they belong to all Departments. Are there any better ways of accomplishing extensiblity? I came across hierarchyId in MS-SQL server which is the db of our choice but haven’t researched into it. Also, need to figure out how to write the queries to get to the Attributes given a Department.

(1)

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 and Attribute can be part of multiple Sub-Category(-ies)