design issue

[freebsdntu]

Hi guys, I am designing a database schema for project, where ptitle,pobjective,etc are the attributes.

A project might have series of sub-project, and this sub-project might have its own sub-projects, and it goes on like this. If I want to easily list the hierachy of all the projects, what should be the shema look like?

Ok, the shcema I am thinking of now is like this:

I inlclude a super-project id field, where it is set to 0 if the project does not have any super project, and it is set to the project id of its direct parent project if it is a sub-project of some project. The problem with this design is I don't know how to list the hierachy.

Any hints on the schema? Thank you very much!

[fenway]

Check out the sticky on normalization and the dev article inside on storing hierarchal data.

[freebsdntu]

Check out the sticky on normalization and the dev article inside on storing hierarchal data.

Thank you for your reply,fenway,I have browsed through some articles on storing hierarchical data, it is too much complicated, when applied to my case, i mean. So is there any other approach? Maybe I should have asked whether would my design work.

[fenway]

Yes, having a parent_id works, for the root node (i.e. the top), set it to NULL (better than 0).  To query it, you simply need to know how many levels you want, and join the same table in that many times.