Default tree structure, within a main tree, with more unique options at the end

Question

So I have this project where I navigate through Categories with the purpose to get to the end of the categories tree. My problem is, is that I want to have many categories with the exact same name. I could have a value on my category to tell my scripts to search the next categories in the "DefaultCategories" table. But my bigger problem is, is that I want to have more unique categories at the end of the default tree.

I've tried to google for solutions, but I'm pretty sure I'm not wording it right, so I can't find any results :(

I've thought of a solution but I am not sure if it is a good one. I'll try to display my idea in an image.

So here is what I have thought of: The drawing on the right (everything outside the box), with the green and red is the main Categories tree and the box with the red tree is the "DefaultCategories" tree. The green vertical lines are all unique values, the red ones are default values. The horizontal lines just show their connection.

As you can see, the DefaultCategories tree is reused in the main tree. How I want to implement this is creating empty categories and assigning a foreign key coming from the corresponding default category. When fetching this category, it checks if there is a default category linked to the category, if so, overwrite it's values with the values from the default category.

By having these empty "substitute" categories I can use them as a parent for new unique categories. (As show by the small green parts at the ends of the main tree)

But now I've run into a problem. I want to update the default category's tree.

For example, I want to add an extra option. And I want everywhere where a default tree is present, to update their structure as well. This is represented with the new blue line:

I trust that doing this in a large tree would be catastrophic for the performance. An even harder query is when I wish to change the parent of one of the default categories. (with finding the right parent categories in the main tree to assign the substitute category to)

Is there a better way to achieve this?

Answer 1

There are two common ways of representing trees in relational databases. One is the "adjacency list", where every record has a "parent_id", or "nested sets" - see this article.. They have benefits and drawbacks, esp. for performance, though most databases support hierarchical queries now.

In addition, you have an inheritance challenge (you have two types of tree, "default" and "custom") - there are 3 common solutions to that.

There's no clean way to achieve what you want just in SQL.

One option is to delegate this logic to the application layer. Define your "default" trees, and inject them into you "custom" trees through application logic. Use whichever tree structure is best for your use case, and apply the "default" logic without managing that in your data model.