Greetings:
I'm creating a simple "to do list" type app for the company Intranet but I have an issue I'd like your advice on. For each task that people can make, I want them to be able to nest tasks under it. This is a bad example but let's say we're going to have a task for "Have a meeting with the marketing manager regarding the press release." Under that, there might be topics of discussion. Then under one of the discussion items, there might be an additional task of, "Bring last month's magazine for review." In that way, we could have nesting 3 levels deep.
I thought about doing this with a table called tasks. There would be an autonumber "id" field, a "parentid" field to capture the nesting, a text field to hold the task, and an "order" field that allows me to sort the tasks based on time or some user-defined order.
The problem I have is that with parentID, when I do the query to bring in the tasks, wouldn't I need to have a query to get all the top level tasks and then a subquery to get the next level, then yet another subquery to get the third level requests?
It just seems like there has to be a better way of doing this so each page doesn't have multiple queries each time the page loads. Is there a better way to do this?
Any tips would be greatly appreciated!
Your advice please on structure of table & how I'll query it
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Your advice please on structure of table & how I'll query it
Search these forums for "adjacency model" or similar. Here is a Sitepoint article:
http://www.sitepoint.com/article/hierar ... a-database
http://www.sitepoint.com/article/hierar ... a-database
(#10850)
Re: Your advice please on structure of table & how I'll query it
The adjacency model is what I was going to do. I think I just had a tough time explaining it. The only thing though is that to output my entire list of tasks, there might be 100 queries on the page to get all of the items. Am I missing something?arborint wrote:Search these forums for "adjacency model" or similar. Here is a Sitepoint article:
http://www.sitepoint.com/article/hierar ... a-database
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Your advice please on structure of table & how I'll query it
Did you read through the whole tutorial? On page 2 and 3 is described "Modified Preorder Tree Traversal" method with which you can get all items hierarchically ordered with one query.