| Author |
Topic |
|
Guennadi Vanine
Starting Member
5 Posts |
 Posted - 2007-07-11 : 23:19:28
|
| I consulted many books, articles, posts on trees and hierarchies realized in SQL (databases)And all of them use ParentID for linking but not ChildID.Why is it? What are possible pitfall, catch33 in basing the trees/hierarchies on linking through ChildID instead of ParentID? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-12 : 04:26:08
|
| because if you go with child id your table isn't normalized._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-12 : 04:32:28
|
| Try designing or coding it and you will see the problem.I suspect it a matter of definition - what do you think the ChildID is?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Guennadi Vanine
Starting Member
5 Posts |
Posted - 2007-07-12 : 07:29:16
|
quote:
Originally posted by spirit1
because if you go with child id your table isn't normalized.
Can you explode it a little bit more?Hierarchies/trees with parent id are not normalized either. The data is not even relational, it is hierarchicalWell, I even cannot see any big differenceMy task is to redesign the database merging all tables in one EAV/CR table, move all existing data into it and create end user GUI.Really I am already stipulated the database architecture on meThis table is hierarchical and self-referncing ... Any attribute may reference the child complex "object", that is multiple attributes (lines) with the same subobject ObjectID. So, the table contains the ChildID column referencing child object attributes (lines) But it is a digression.My question is about hierarchical databases (in this or not this context).Since I need some effective algorithms to deal with (this) tree/hierarchy I consulted books, articles, posts on trees and hierarchiesAnd all books, articles, posts use for hierarchies/trees only the ParentID for linking but not ChildID.But still I cannot see why it is and where is the difference (when there is only parent id, the reference is in top direction, and with child id it is to bottom) The situation is completely simmetrical but just reversed. One may call bottom by top, root by leaf and vice versa, they are just the names. I am just afraid that I miss something and would like to be sure that I do not start elaborating the code for flawed design.What do I miss? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-12 : 08:05:06
|
| Usually in a hierachy you can have many children for a parent but only one parent for a child so you get a tree structure.Using this model you place the parent ID on the entry.If you wish to have many parents for a single child and many children for a single parent then theis design does not work - one possibility is to have a separate table to hold the links between the parents and children.How you access this structure depends on the result you want you might want to get all the children in the tree below a parent or you might want all the trees of which an entry is part.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Guennadi Vanine
Starting Member
5 Posts |
Posted - 2007-07-15 : 21:58:58
|
quote:
Originally posted by nr
Usually in a hierachy you can have many children for a parent but only one parent for a child so you get a tree structure.Using this model you place the parent ID on the entry.
Well, I wrote incorrectly - I really have only one parent for a child.What is wrong in using child id but not parent id? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-16 : 05:20:34
|
| How are you going to represent multiple children using a child ID.create table entity(id int, parent int)insert entity select 1, 0insert entity select 2, 1insert entity select 3, 1that gives a parent with two children.How could you do that with the child ID?create table entity(id int, Child int)insert entity select 1, 2insert entity select 1, 3insert entity select 2, 0insert entity select 3, 0would be ok if this was just a conjoint table maybe but very wasteful if it held other attributes.Difficult to maintain too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Guennadi Vanine
Starting Member
5 Posts |
Posted - 2007-07-16 : 10:52:23
|
| Really for table entity(id int, Child int),it will be 3 statements insert entity select 1, 2 insert entity select 1, 3 insert entity select 0, 1and not 4 your statements insert entity select 1, 2 insert entity select 1, 3 insert entity select 2, 0 insert entity select 3, 0So both methods are equivalentHaving childId seems more intuitive, descriptive and easy for me |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-16 : 11:02:33
|
| until you get a business request that a parent can have more than 1 child and you realize that you've shot yourself in the foot._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-07-16 : 11:18:13
|
| >> until you get a business request that a parent can have more than 1 child and It is erpresented hereIn your representation just rename Child to ID and ID to ParentID.It's just more natural to most people because the ID lables the entity - in your representation the childID is labelling the entity and the ID is labelling it's parent.You're right in that this doesn't matter - it's just a difference in column names.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
hshot_rooke
Starting Member
8 Posts |
Posted - 2007-10-04 : 16:08:42
|
I wish I could post this picture I have, but we use hierarchy structure for many tables in our office. Our idea comes from "Advanced Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau, Ph.D.It explains a self referencing hierarchical table. Some limitations, but basically maintains itself and can handle parent/child hierarchies as deep as you need to be.Wonder if this will show up ok. I know it doesn't mean much, but basically you can query where a child is contained using LIKE '%.ChildID.%'It's a good solution for our needs, check out the book...there is too much to explain in a forum post...To explain, each category below has an ID field as a number. Everytime you instert into the table, a trigger maintains the level and hierarchy for you. You can see the hierarchy string below. This is used to query out children, parents, children 2 levels below, etc.ROOT Action zzabc Affairs/Love Triangles Western test5 TV Based on Comic Book American Civil War Based on Comic Book Torture Box Set Bugs Ballet Dark Comedy Crime blahblah6 Based on TV Show Children Christmas Classics BBC Ballet American Revolution .0..0.110..0.110.131..0.110.131.113..0.110.131.113.132..0.110.131.113.132.126..0.110.131.118..0.110.131.118.128..0.110.131.129..0.110.131.129.130..0.110.131.129.130.133..0.110.131.129.130.134..0.110.131.129.130.135..0.110.131.129.130.136..0.110.141..0.110.142..0.137..0.138..0.139..0.140..0.143..0.144..0.145. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-05 : 10:51:03
|
| We store:ID of Parent and next-higher sibling (NULL if 1st / "eldest" childSequence number - a number which provides "walking order" for the tree, across all rows in the table.So we can select all descendants of a given ID by:SELECT ...FROM ...WHERE PATH LIKE '123.456.%'ORDER BY SequenceColumnWe store the Hierarchy data in a separate table (one:one with the main table) so that activity on the hierarchy table does not need to be audit-logged etc.We do not update this information with a trigger (but instead we do it in the Sproc that Inserts/Changes row data) so that we can suppress updating all the associated records if there is, for example, a bulk import to the table:Disable PATH & Sequence updating (in effect)Bulk insert / updateRecalculate PATH & SequenceKristen |
|
|
|
hshot_rooke
Starting Member
8 Posts |
Posted - 2007-10-05 : 11:23:52
|
| Kristen, very interesting. You are using similar schema to us.Yes, for bulk updates, in some cases the trigger needs to be disabled beforehand.Did you develop this solution yourselves? We got the idea from a book. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-05 : 11:48:42
|
| Yup. Researched numerous ways of doing it, then built our own.We actually use a special ID (we call NodeID, and which can change, unlike the PK ID), and NOT the ID of the record, and we adjust the NodeIDs so that they are guaranteed ascending sequence. So that we can Sort by the Path too ...Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-05 : 11:49:41
|
| One of our other constraints is that the records need to be movable to disconnected servers, so we use GUID for Parent/Sibling ID and can insert "limbs" of the tree and then rebuild the Hierarchy Path/Sequence etc.Kristen |
|
|
|
vgv8
Starting Member
6 Posts |
Posted - 2007-12-19 : 02:13:01
|
quote:
Originally posted by nr
Usually in a hierachy you can have many children for a parent but only one parent for a child so you get a tree structure.Using this model you place the parent ID on the entry.If you wish to have many parents for a single child and many children for a single parent then theis design does not work - one possibility is to have a separate table to hold the links between the parents and children.
Well, I have done the Transact-SQL Stored Procedure (for MS SQL Server) using ChildID.All worked OK for me.It is EAV or "open schema" case having just one table and even all of the data is in one column of the same type sql_variant. In my case I have many children.For each children I have many lines in table since each child have many attributes and many subchildren (subobjects). Now I'd like to understand what is wrong to have many chilren and many parents?Note that it is hierarchical and not relational structure/schema.No intermediate table is neededAll is in one table Even if to have 2-3 tables for this case I cannot understand where is the potential problem?The structure is just symmetrical for browsing (recursing) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-12-26 : 21:31:18
|
| >> Even if to have 2-3 tables for this case I cannot understand where is the potential problem?No problem - just use what best suits the business and your experience.>> Now I'd like to understand what is wrong to have many chilren and many parents?If you hold the links on the row that also holds the entity data then you can hold one child and/or one parent - you can't hold multiple links without duplicating the rest of the data (or making a virtual table).Doesn't matter how you represent things as long as it works in your environment.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
parentID vs. childID