Self-referential hierarchy

As explained in The Well-Grounded Python Developer: How the pros use Python and Flask

Self-referential hierarchical data is data whose records reference other records in the same dataset to represent parent-child relationships.

A self-referential hierarchy stores parent-child relationships when similar records belong in one table. A post can contain comments, and a comment can contain more comments without adding a table for every nesting level. section 11.7

Model the hierarchy by giving each row a reference to another row in the same table:

• Add a parent_uid field to Post and make it a foreign key to post_uid in that same table. section 11.7
• Treat rows with a NULL parent_uid as root content posts. section 11.7
• Treat rows with a non-NULL parent_uid as child comments that may reference either content or another comment. section 11.7
• Use the resulting one-to-many relationship to represent each row and its child rows. section 11.7

Nested comments

A content post is the root row. A comment points to that post, while a reply points to the comment. The database can support additional nesting; limiting comments to two levels remains an application decision. section 11.7

Figure 11.6 The self-referential Post table creates a hierarchal content/comment structure.

Use the table diagram to see how one Post table forms the content-and-comment hierarchy. section 11.7

For deeper traversal, combine SQLAlchemy and Python with common table expressions and recursion. section 11.12

How The Well-Grounded Python Developer: How the pros use Python and Flask covers it

How deep it goes

Defines the same-table foreign key and root-child rules, then points to CTE recursion for deeper traversal.

Examples

Uses content posts, comments, replies, parent_uid, and the self-referential Post table as concrete artifacts.

What you can do after

Apply it via SQLAlchemy and Python to represent nested content and retrieve deeper levels with recursive CTEs.

How it connects

Connects foreign keys, one-to-many relationships, SQLAlchemy, common table expressions, and recursion.

liveBookmanning.com

Read The Well-Grounded Python Developer: How the pros use Python and Flask on liveBook Get the book at manning.com

Related concepts

• recursionaddressed by · Recursion is a computational technique in which a definition or procedure refers to or invokes the same definition or procedure, directly or indirectly.
• common table expressionaddressed by · A common table expression is a named temporary query result defined within a SQL statement and available for reference by that statement.

This explainer was generated by Manning's knowledge graph from the text of The Well-Grounded Python Developer: How the pros use Python and Flask. The section links point to the parts of the book it draws on.