tables that refer to themselves

[dogeht]

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 

 

[aschk]

I'll point you to the "ULTIMATE SOLUTION":

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[dogeht]

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?

[fenway]

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.

[aschk]

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.

[fenway]

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".

[aschk]

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 ...

[dogeht]

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?

[aschk]

AH yesh!

 

I've seen this before. Bookmarked now

It seems to work. Add another model to your options, try all 3.