Database for inheritance hierarchy queries

Question

I'm looking for a way to store hierarchical data, in a way that will facilitate "inheritance" queries that will coalesce upwards in the hierarchy.

Simple example:

Level 1, property X = 123
 - Level 2, property x  = null
 - - Level 3, property x = null

What is the effective value of property x at level 3? Answer: 123 (because it coalesces up to level 1).

Is there a database engine that is purposely made for this type of structure? This can be done relationally, or possibly in a graph, with some effort, but I am trying to find an engine natively optimized for this.

For a graph database, you can use Apache AGE, which is a graph extension for PostgreSQL. They also have a branch for label inheritance. — Matheus Farias, Oct 20 '23 at 15:59

score 0 · Answer 1 · answered Jul 07 '23 at 17:55

Is there a database engine that is purposely made for this type of structure?

There are multiple ways to store hierarchical data in relational database. Simplest to understand and great to search is materialized path (similar to the file system).

More normalized approach is to use (id, parent_id) - it is better for updates but harder to query.

Database for inheritance hierarchy queries

1 Answers1