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
