0

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.

nonopolarity
  • 146,324
  • 131
  • 460
  • 740
  • You can have Employee-to-Employee table instead of parentID column. I don't know which one is better. – eugeneK Jun 06 '10 at 06:57

3 Answers3

2

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).

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
1

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

Employee    Supervisor
1           2
2           1
Simon
  • 9,255
  • 4
  • 37
  • 54
  • That's false. You CAN check it. Just very inefficiently. – DVK Jun 06 '10 at 07:01
  • I wrote: "You have to check this by hand"... what I mean is that the DBMS can't do that for you. – Simon Jun 06 '10 at 07:02
  • Yes it can. Just not very fast (e.g. you need a loop) – DVK Jun 06 '10 at 07:03
  • Yes... but you have to program the loop e.g. in a stored procedure. What I mean is, you still need to inject some logic manually. You cannot rely on reference/integrity rules. – Simon Jun 06 '10 at 07:05
0

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)

DVK
  • 126,886
  • 32
  • 213
  • 327