The table that points at itself

Almost every schema has one somewhere. A category that belongs to a broader category. An employee who reports to another employee. A comment replying to a comment. A part made of other parts.

The shape is always the same and it always looks slightly wrong the first time: one table, and a foreign key that points at that same table's primary key.

Why it's one table and not several

The alternative is a table per level — departments, then teams, then sub_teams — and it works right up until someone needs a fourth level. Then it's a migration, a new view, and every query rewritten.

A self-reference has no levels to run out of. Each row names its parent, and the depth is whatever your data happens to be. Adding a fifth tier to a product taxonomy is a row, not a schema change.

Building one

In the relationship picker, your view's own table now appears first in Other Entities, labelled this table. Pick it, choose the column to reference — usually the primary key — set a Display Field so the column shows a name rather than a number, and SchemaStack adds the foreign key and the relationship the same way it would for any other reference.

The reverse side is created for you, as it is for every relationship, so a category also knows about the rows pointing at it.

Reading up the chain

A lookup can follow the same relationship more than once. From a category you can show its parent's name, and then its grandparent's name, and the grid resolves both:

Gaming Laptops → parent Laptops → grandparent Computers

Root rows keep their place. A category with no parent shows a blank cell, not a missing row — the join is a LEFT JOIN, so nothing drops out of your grid for the crime of being top-level.

What was actually in the way

This is the part worth being straight about, because self-references were readable on import long before they were creatable.

The path resolver refused any lookup that visited the same table twice, and raised a cycle error when it saw one. That sounds prudent. It wasn't: the resolver walks a stored, finite list of hops — the path you picked, which cannot grow while it is being walked — and a separate depth cap has always been the thing that bounds it. The check wasn't protecting termination. It was asserting that hierarchies don't exist.

parent.parent.name legitimately visits the same table three times. Removing the check turned a whole category of ordinary schema into something the product could model. The depth cap kept doing the job it was already doing.

What it doesn't do (yet)

• No self many-to-many. A table linked to itself through a join table — "related products", "users who follow users" — is not offered: the junction's two foreign keys would derive the same column name from the same table. Model it with an explicit junction entity of your own, which also lets you put columns on the link.
• Lookups reach five hops from the picker. Deeper hierarchies live in your data perfectly happily; the picker just won't build a single column that reaches past the fifth ancestor. The underlying resolver's cap is far higher, so this is a picker bound rather than a storage one.
• No "show me every descendant". Walking down an arbitrary number of levels — every sub-category under Electronics, however deep — is a recursive query, which is a different feature from following a relationship a fixed number of times. Counting direct children works today.
• The automatic reverse side is named after the table, not the role. On a self-reference both directions live on the same table, so when the reverse name would collide with the forward one it gets qualified with the foreign key column instead — accurate, but categories_parent_id is not the word "children". Renaming it is a manual edit.
• Nothing stops you creating a loop in your data. Point A's parent at B and B's parent at A and the database will store it; the grid will show each as the other's parent. The lookup terminates safely because it follows a fixed number of hops, but no validation warns you that the hierarchy no longer has a root.

The relationships guide covers the picker and the display field, and the join table is a real table covers the other direction — what happens when two different tables need to be linked many-to-many.