Hello All

I am looking for insights, opinions, pros/cons, etc
regarding having a parent id field within a table self referencing
itself to children
within that same table versus setting up another table that houses the
parent ID's
relating back to the other table that houses the children

Thoughts?

Thanks
Tom

On Feb 11, 2:16*pm, Tom Lehr <tomc... (AT) gmail (DOT) com> wrote:
Hmmm ... theory would be "Parent Table / Child Table" i.e. a 1 => Many
or even a 1 => 1 relationship.

Give an example - i.e. what constitutes a "parent" and a "child" and
how are these represented as an identical entity if in a single table?

I s'pose something like :

Person1 -> ID1, GrandMum ID2
Person2 -> ID2, GrandMum NULL
Person3 -> ID3, GrandMum ID1

To have Daughter -> Mum -> GrandMum??

The self referencing parent/child table is a standard paradigm. When it
fits the world, there is no reason to shoehorn the world into another
model. Informix 11.70 even has new syntax and optimization to better
support these kinds of self-referencing relationships.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Fri, Feb 11, 2011 at 9:16 AM, Tom Lehr <tomcaml (AT) gmail (DOT) com> wrote:

On Feb 11, 4:17*pm, Art Kagel <art.ka... (AT) gmail (DOT) com> wrote:
I agree with Art - we used to use this "self-referencing parent/child"
example "back in the day" when teaching the MNO and SQL classes. BOM
explosions are an example of this paradigm in use.

Thanks -
Mark Scranton
The Mark Scranton Group

All -- - - Thanks much!

Tom