jamesaoverton / cmi-pb-terminology

CMI-PB Controlled Terminology
0 stars 0 forks source link

Prototype `tree` structure #28

Closed jamesaoverton closed 2 years ago

jamesaoverton commented 2 years ago

For this project, like previous VALVE work, we want to validate that a given value falls under another value in a simple hierarchy. We use a tree() function to define the hierarchy, then the under() function to specify which tree and which value must be an ancestor.

As in previous VALVE work, we specify the tree(column) structure on the "parent" column, and the argument is the name of the "child" column. Each child may have a parent, and each parent must appear in the child column. Then we can use a WITH RECURSIVE query to get all the ancestors (or descendants, children, parents) of a given node.

For this to work:

  1. the child column must have distinct values (uniqueness)
  2. the child column must act as a foreign key constraint for the parent column ("foreign" sounds strange since the two columns are in the same table)
  3. the parent column must have the same datatype as the child column
  4. the tree should not contain cycles

Eventually we want to support multiple parents, which changes our tree to a directed acyclic graph (DAG).

lmcmicu commented 2 years ago

Note to self: I previously have written up some notes about tree here: https://github.com/ontodev/valve/blob/valve-sql-investigation/valve-function-investigation.org#tree

jamesaoverton commented 2 years ago

Resolved by #30