Your advice please on structure of table & how I'll query it
Posted: Tue Apr 01, 2008 8:32 pm
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!
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!