Parent-Child constraints
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...@gmail.com> wrote: > 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 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@iiug.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@gmail.com> wrote: > 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 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
On Feb 11, 4:17 pm, Art Kagel <art.ka...@gmail.com> wrote: > 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 (a...@iiug.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 <tomc...@gmail.com> wrote: > > 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 > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list 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