dogeht Posted February 26, 2008 Share Posted February 26, 2008 hi, exscuse for my english, i'm italian. what is the best way to manage tables that refer to themselves? ---> for example, I have this table: PROJECT ( id_project, title ) now imagine that the project [ 1 ; "build a boat" ] is formed by 3 subprojects [ 2 ; "build the hulk" ] , [ 5 ; "sew the veil" ] , [ 6 ; "find a life jacket" ] and the project/subproject 2 is formed by another 2 subprojects [ 3 ; "find the wood" ] and [ 7 ; "find the nails" ] I can make tens of these example ( a mechanisme and its sub-mechanismes, an organization and its sub-organizations, etc... ) ---> how can I make my tables in order to manage this kind of problem? hypothesis 1: adding a field in the same table PROJECT ( id_projetc, title, fk_project_father ) hypothesis 2: creating an another table PROJECT ( id_projetc, title ) PROJECT_HIERARCHY ( fk_project_father, fk_project_son ) or what??? thank you very much for every ideas Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/ Share on other sites More sharing options...
aschk Posted February 26, 2008 Share Posted February 26, 2008 I'll point you to the "ULTIMATE SOLUTION": http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-476873 Share on other sites More sharing options...
dogeht Posted February 26, 2008 Author Share Posted February 26, 2008 thank you aschk I've already studied that article, and I think the NESTED SET MODEL is a good way. have you ever used this model in your experience? Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-476897 Share on other sites More sharing options...
fenway Posted February 26, 2008 Share Posted February 26, 2008 I prefer simply storing a "parent_id"... yes, it means you have to know how many levels there are, but that's usually the case anyway. Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-476937 Share on other sites More sharing options...
aschk Posted February 26, 2008 Share Posted February 26, 2008 I've not had a use for it yet. The first design i did using the parent/child model was using fenway's method of simply storing the parent_id, HOWEVER i found this model seriously limiting. Unfortunately for me I came across the nested model too late, and having read about it now (over and over in various places) i'm convinced it is the best option to take now. What you could do (time prevaling) is to try both methods and let me know how they work out. I don't like the parent_id model because it means you need another additional LEFT JOIN for descend another level. Whereas the nested model allows you to search by depth (or unlimited depth) without the need for another LEFT JOIN. Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-476965 Share on other sites More sharing options...
fenway Posted February 26, 2008 Share Posted February 26, 2008 I don't like the parent_id model because it means you need another additional LEFT JOIN for descend another level. True, but it's *so* easy to update the hierarchy without too much sql magic (i.e. can be done by hand), it's it's impossible to "corrupt". Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-477066 Share on other sites More sharing options...
aschk Posted February 26, 2008 Share Posted February 26, 2008 I agree, inserting is easy with the parent_id model. Just doing some tests with the nested set model and i'm liking it so far. I guess it's a case of finding what works for you dog ... Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-477148 Share on other sites More sharing options...
dogeht Posted February 27, 2008 Author Share Posted February 27, 2008 i've read many articles about hierarchy in a database. lots of people (first of all joe celko) suggest using nested set model. this article instead, explain how to make easier work with an adjecency model without using recursive join: http://www.sqlteam.com/article/more-trees-hierarchies-in-sql what do you think about this article? could be dangerous to store the structure of the tree in a table? Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-477893 Share on other sites More sharing options...
aschk Posted February 27, 2008 Share Posted February 27, 2008 AH yesh! I've seen this before. Bookmarked now It seems to work. Add another model to your options, try all 3. Link to comment https://forums.phpfreaks.com/topic/93068-tables-that-refer-to-themselves/#findComment-477905 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.