To Do List Structure Question

[ejstefl]

Hello all,

I'm looking for some table structure advice. I'm making a database that will track several To Do's. I want to have different List names (Projects, Next Actions, Waiting For, ect.). I then want to click on one of my lists to display the items in the list, which can have sub items on them. Also, each sub-item can have a sub-item, et al. As an example, on my Next Actions list:

At Work
├ Do This
├ And This
├ Plus This
Phone Calls
├ Call Joe
├ Call Bob
At Status Meeting
├ Ask About Project X
├ Request Project Y Update
│ ├ Update Financial
│ └ Update Timline
└Propose Project Z

OK, so I am envisioning the following table structure:

tblLists
pk ListID
List Name

tblListDetail
pk ListDetailID
fk ListID
ListDetailName
ListDetailParentID <-- this would be the ListDetailID of the parent, null if there is no parent

So, in my example, if ListID = 1 was Next Actions, then my tblListDetail would look look:

ListDetailID, ListID, ListDetailName, ListDetailParentID
1, 1, At Work,
2, 1, Do This, 1
3, 1, And This, 1
4, 1, Plus This, 1
5, 1, Phone Calls,
6, 1, Call Joe, 5
7, 1, Call Bob, 5
8, 1, At Status Meeting,
9, 1, Ask about Project X, 8
10, 1, Req Project Y Update, 8
11, 1, Update Financial, 10
12, 1, Update Timeline, 10
13, 1, Propose Project Z, 8

Does this table structure make sense? Is there a better way?

Thanks in advance!!

 

[andrew93]

Hi

This table structure almost makes perfect sense and is appropriate for the multiple hierarchy situation that you described. I'm not sure of the necessity of the 'List ID' field where every value is 1, it seems to be redundant unless you have multiple lists (which is the point of the hierarchy anyway). Rather than have multiple records with a null value for the 'ListParentDetailID' field, you could have something like this instead to give you multiple lists with hierarchies :

ID, Description, ParentID
1, My To Do List
2, To Do At Work List, 1
3, To Do At Home List, 1
4, Do This, 2
5, Do That, 2
etc.

This way you wouldn't need the list table, because it is all defined in the one table. I may be stating the obvious, but you should have other fields such as 'Due Date', 'Completed Date' etc. The outstanding tasks where the 'Completed Date' is null, and the overdue tasks are those where the 'Due Date' is less than Today (i.e. <Date() in a query) and the 'Completed Date' is null. I discussed a very similar table setup in another post here that may help.

Andrew

 

[ejstefl]

Andrew,

Thanks for the help. You are, of course, correct - I do not need tblLists. Although I do plan on having multiple lists, that can be accomplished using just one table.

Also, I do plan on having certain other fields, such as due date, but didn't include them on the structure for the sake of clarity.

I'm hoping to present this all with a treeview... we'll see how that goes.

Thanks for the help!