Should I use a Foreign Key to Show Tree Relationship in SQL

Question

I am trying to model a tree relationship in a table. For instance, there are "Categories" and categories can themselves be inside a Parent category.

My schema is:

id int PRIMARY KEY,
parent_id int,
name

My question is, should I label the parent_id column as a Foreign key? Foreign implies "outside" and not self-referencing. Is there a different type of key for this purpose?

My question is similar to: http://stackoverflow.com/questions/528529/self-referencing-constraint-in-ms-sql, but I'm asking a different question, cascading not being an issue.

Answer 1

Self-referencing foreign keys happen all the time. E.g. an employee might have another "employee" as his manager, so the manager_id will be a foreign key to the employee_id field in the same table.

Foreign keys are the natural candidate for representing the parent node in hierarchical data, although they're not exclusively used for that :)

Answer 2

I don't believe there is another type of key... a foreign key would be fine in this scenario.. it would enforce the constraint against the parent_id to ensure it references a valid id

Answer 3

If you have very deep levels of nesting it may not be easy to performantly select out all descendants of a particular node, since most DB's do not handle recursion very well. Another approach is to use what's called the "Nested Set Model" to represent the relationships. A great article is available here:

http://www.intelligententerprise.com/001020/celko.jhtml

Answer 4

A foreign key between two columns in the same table is often used when mapping tree structures to relational databases. However, it is not the only approach avaiable.

See this article for alternative reperesentations: Storing Hierarchical Data in a Database

Answer 5

this is a stupid article