Untested and only from the SQL compiler in my brain -- This compiler is known to have a few bugs -- It may also be too late to go use this, so this might be something to look at if you plan on upgrading;
This is the first way I'd do it; create table Projects (ProjectID INTEGER PRIMARY KEY AUTOINCREMENT, ProjectName char); create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, TaskID integer, TaskName char, Completed Bool, DateCompleted DateTime); create table ProjectNotes (NoteID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, fkTaskID integer, NoteInfo Char, PostDate CurrentTimeStamp); create index idxfkProjectID on Tasks (fkProjectID); create index idxfkDateCompleted on Tasks (DateCompleted); create index idxfkNoteProjectID on ProjectNotes (fkProjectID); create index idxfkNoteTaskID on ProjectNotes (fkTaskID); create index idxfkNotePostDate on ProjectNotes (PostDate); create view IncompleteTasks as select ProjectID, ProjectName, TaskID, TaskName from Projects join Tasks on Projects.ProjectID=Tasks.fkProjectID; create view CompletedTasks as select ProjectID, ProjectName, TaskID, TaskName, DateCompleted from Projects join Tasks on Projects.Project=Tasks.fkProjectID; create view ShowProjectNotes as select ProjectID, ProjectName, NoteID, NoteInfo, PostDate from ProjectNotes join Projects on Projects.ProjectID=ProjectNotes.fkProjectID where fkTaskID=0 or isnull(fkTaskID) order by PostDate; create view ShowTaskNotes as select TaskID, TaskName, NoteID, NoteInfo, PostDate from ProjectNotes join Tasks on Tasks.TaskID=ProjectNotes.fkTaskID where fkProjectID=0 or isnull(fkProjectID) order by PostDate; --- Caveats: - This method assumes that a task cannot have a subtask. A project can have multiple tasks, but tasks cannot have subtasks. - If implementing a search, there is no index on either project name or task name, so, on a search, you'll be doing a full table scan for LIKE '%TERM%'. - The ProjectNotes has a PK assigned simply because I hate updating fields based on a FLOAT value (Which is what PostDate is) and there would be no reliable way to update or delete a note if necessary. Not to mention, depending on how you're informing the user (IE: Listbox) you can more easily assign an integer to a row than you can a float. Notes: - Note no index created for the ProjectID or the TaskID. That is already handled by the PRIMARY KEY function - The AUTOINCREMENT keyword is used so when you do an insert a new ID is created for that project or task - Following the guidelines posted previously about something along the lines of "If you've got multiple tables with the same column definitions, its usually bad design", and although I can't QUITE get on board with that (Another topic), this table will store general notes about the project at hand, as well as notes done on each particular task, all in one table. When inserting a note, you just need to assign either the fkTaskID or the fkProjectID, as well as the comment, and thats it. --- Another way would be like this; create table Events (EventID integer primary key autoincrement, ParentID integer default 0, EntryName char, Completed Boolean, DateCompleted DateTime); create table EventNotes (NoteID integer primary key autoincrement, EventID integer, NoteText char, PostDate currenttimestamp); create index idxParentID on Events (ParentID); create index idxEventNote on EventNotes (EventID); --- Caveats: - This will introduce to have subtasks for a project. If Events.ParentID=0 then this is the primary project. Assigning the ParentID a value to an existing EventID will mean that it is, by your general definition, a task. But you can assign ParentID to another task. You can keep doing this over and over so your one project can have a theoretical unlimited number of subtasks. - Your code will need to be restructured to recursively get the all subtasks, or, at least an introduction of a new function to get the details for the next set of subtasks. --- Notes: - You'll need to figure out how to create the relationship between the EventID and ParentID so that if an event gets deleted, so will all the sub-events. Its simple enough to do, but my brain-compiler doesn't have the references to do that kind of magic at this time. ;) Look up CASCADE DELETE. Thanks everyone for the help. The problem that I have is that I have > projects that have tasks. For example: > project 1 > task 1 - File Processing/language de > task 2 - File Processing/language es > task 3 - File Processing/language it > task 4 - Translation/language de > task 5 - Translation/language es > task 6 - Translation/language it > task 7 - Publishing/language de > task 8 - Publishing/language es > task 9 - Publishing/language it > project 2 > task 10 - File Processing/language de > task 11 - File Processing/language es > task 12 - File Processing/language it > task 13 - Translation/language de > task 14 - Translation/language es > task 15 - Translation/language it > task 16 - Publishing/language de > task 17 - Publishing/language es > task 19 - Publishing/language it > and so forth. Each of these task have also open/closed as well as the > project. For example, task1 belongs to project 1. Once task 1 is > complete, then, it becomes complete or closed, but task 2 may still be > open. After task 1 is closed, then, task 4 starts rolling and so forth. > Also, each tasks must have an uniqueID that task has a vendor and has a > vendor charge against it, so it needs to have an specific ID for the vendor > to charge against it and we have a unique trace of that task. We also must > keep finances based on projects, as well as task, so these tasks must also > be connected to a ProjID. > > I know I have a lot of work to do, but, this indexing are helping a lot. > Thanks for your loving support. :-) > > josé > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
