freebsdntu Posted December 19, 2007 Share Posted December 19, 2007 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! Quote Link to comment Share on other sites More sharing options...
fenway Posted December 19, 2007 Share Posted December 19, 2007 Check out the sticky on normalization and the dev article inside on storing hierarchal data. Quote Link to comment Share on other sites More sharing options...
freebsdntu Posted December 20, 2007 Author Share Posted December 20, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 20, 2007 Share Posted December 20, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.