Your advice please on structure of table & how I'll query it

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ChrisF79
Forum Commoner
Posts: 26
Joined: Tue Apr 01, 2008 8:26 pm

Your advice please on structure of table & how I'll query it

Post by ChrisF79 »

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!
User avatar
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

Post by Christopher »

Search these forums for "adjacency model" or similar. Here is a Sitepoint article:

http://www.sitepoint.com/article/hierar ... a-database
(#10850)
ChrisF79
Forum Commoner
Posts: 26
Joined: Tue Apr 01, 2008 8:26 pm

Re: Your advice please on structure of table & how I'll query it

Post by ChrisF79 »

arborint wrote:Search these forums for "adjacency model" or similar. Here is a Sitepoint article:

http://www.sitepoint.com/article/hierar ... a-database
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?
User avatar
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

Post by EverLearning »

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