Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Joe Celko's Frammis example - Nested set models
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mnorton
Starting Member

7 Posts

Posted - 05/30/2001 :  11:57:21  Show Profile  Reply with Quote
I've recently been trying to figure out how to do a parts breakdown into subassemblies. The example that I am trying to emulate for my own application is Joe Celko's frammis example that talks about Nested set models. I understand the concept behind his model but what I don't understand is the rgt and lft column and how he comes up with the numbers that he does for each part. If someone could explain this to me I would greatly appreciate it. Thanks for any help you can provide.

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 05/30/2001 :  12:46:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
Ah yes, Mr. Celko's black magic tree structure :) Better people than you and I have shaken their heads trying to figure that one out.

Go to http://www.intelligententerprise.com/authors/search_Celko.shtml and check Joe's articles; there's one in there on trees. There used to be an article archive for him, if you can't find it there also check www.celko.com

Basically, you input the lft and rgt values into the table at the time of INSERT; I don't believe the structure is meant to calculate them, you sort of just create the appropriate values. He has written procedures to insert and delete nodes from the tree structure, and it handles renumbering, but it doesn't seem to just create the values out of thin air. SQL for Smarties, 2nd Edition has all of the relevant info, I strongly recommend picking it up if you haven't already ( www.sqlteam.com/store.asp ).

FWIW, if the book still doesn't explain it clearly enough for you, I would recommend not using that structure. God knows I've tried and failed; I ended up using other structures that are more practical. Check the SQLTeam FAQ, graz has several articles on hierarchies, trees, and threaded discussion structure, maybe that will work better for you.

Or with luck, Joe will see this and answer it!

Go to Top of Page

mnorton
Starting Member

7 Posts

Posted - 05/30/2001 :  16:08:10  Show Profile  Reply with Quote
I appreciate your reply. Yes I have the book and no I'm still scratching my head about it. I don't think that a tree will work as well as this Frammis model since I have to do exactly what Celko is doing in his example(adding up all the leaf nodes lotsizes to the root nodes lotsize for a total lotsize for that assembly number). So as you can see it is imperative that I figure this out. I have also looked through the archives and the discussions on this webpage at what you suggest and have not found anything else that I think would be suitable. I have tried the hierarchy but haven't figured out how to update the lotsizes of the subassemblies to get the final lotsize for the assembly part. If you know of any other suggestions, I'm open to them. Thanks.

Melanie



quote:

Ah yes, Mr. Celko's black magic tree structure :) Better people than you and I have shaken their heads trying to figure that one out.

Go to http://www.intelligententerprise.com/authors/search_Celko.shtml and check Joe's articles; there's one in there on trees. There used to be an article archive for him, if you can't find it there also check www.celko.com

Basically, you input the lft and rgt values into the table at the time of INSERT; I don't believe the structure is meant to calculate them, you sort of just create the appropriate values. He has written procedures to insert and delete nodes from the tree structure, and it handles renumbering, but it doesn't seem to just create the values out of thin air. SQL for Smarties, 2nd Edition has all of the relevant info, I strongly recommend picking it up if you haven't already ( www.sqlteam.com/store.asp ).

FWIW, if the book still doesn't explain it clearly enough for you, I would recommend not using that structure. God knows I've tried and failed; I ended up using other structures that are more practical. Check the SQLTeam FAQ, graz has several articles on hierarchies, trees, and threaded discussion structure, maybe that will work better for you.

Or with luck, Joe will see this and answer it!





Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 05/30/2001 :  16:15:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
Well I dug into the archive and found these three articles:

http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html

There is an archive at Joe's IntelligentEnterprise page (on the left near the bottom, called DBMS Archive). Anything in 1996, I think there are 4 articles total, including the three I listed here. One of them has a formula for converting from adjacency to nested sets, and other stuff.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000