In this essay, we will model taxonomy on top of a relational database, and as a specific example, we will try to build Udemy's Taxonomy. The primary focus of this essay is to understand how to design taxonomy on top of SQL-based relational DB, define and write queries that are computationally efficient along with deciding indexes on the designed tables.
One interesting area worth exploring here is recursive queries which are supported by most of the major databases (for about 10+ years now). For example, here is a query that will give you the full list of topics along with their paths for arbitrarily deep nesting (not just 3 as in the example you've taken):
One interesting area worth exploring here is recursive queries which are supported by most of the major databases (for about 10+ years now). For example, here is a query that will give you the full list of topics along with their paths for arbitrarily deep nesting (not just 3 as in the example you've taken):
WITH RECURSIVE rec(id, name, parent_id) AS
(SELECT id, name, 1 as level, name AS path
FROM topics
WHERE parent_id IS NULL
UNION
SELECT t.id, t.name,
r.level + 1 AS level,
r.path || ' > ' || t.name AS path
FROM topics t, rec r
WHERE t.parent_id = rec.id
)
SELECT * FROM rec;
I've also computed the "level" here, which you can use to constrain the results to be just the first 3 levels, if necessary.
Similar recursive queries can be written for all the examples you've provided.