Can a binary tree or tree be always represented in a Database as 1 table and self-referencing?

Question

I didn't feel this rule before, but it seems that a binary tree or any tree (each node can have many children but children cannot point back to any parent), then this data structure can be represented as 1 table in a database, with each row having an ID for itself and a parentID that points back to the parent node.

That is in fact the classical Employee - Manager diagram: one boss can have many people under him... and each person can have n people under him, etc. This is a tree structure and is represented in database books as a common example as a single table Employee.

Answer 1

Yes, you can represent hierarchical structures by self-referencing the table. Just be aware of such situations:

Employee    Supervisor
1           2
2           1

Answer 2

Yes, that is correct. Here's a good reference

Just be aware that you generally need a loop in order to unroll the tree (e.g. find transitive relationships)

Answer 3

The answer to your question is 'yes'.

Simon's warning about your trees becoming a cyclic graph is correct too.

All the stuff that has been said about "You have to ensure by hand that this won't happen, i.e. the DBMS won't do that for you automatically, because you will not break any integrity or reference rules.", is WRONG.

This remark and the coresponding comments holds true, as long as you only consider SQL systems.

There exist systems which CAN do this for you in a pure declarative way, that is _without you having to write any code whatsoever_. That system is SIRA_PRISE (http://shark.armchair.mb.ca/~erwin).