Need a little help with table design

S

Scoop

I'm trying to put together a database and can't quite come up with a
table structure that accomplishes what I want.

I am basically trying to track projects and tasks associated with each
project. The difficulty I'm having is that there are different types
of projects and each has a pre-defined set of tasks that come from a
master table of tasks. I want to be able to, for any given project,
list all of the tasks that need to be performed and monitor the status
of each task. For the table that stores the status for the tasks, I
want to maintain referential integrity to ensure that a task cannot be
created for a project unless the task is associated with the
particular type of project.
 
J

Jeff Boyce

Scoop

Is there a reason you aren't using a tool designed specifically to manage
projects? While it will certainly be possible to re-create the wheel and
invent an Access database to do this, why?!

(and if I recall correctly, it is possible to use Access to "see" records in
something like MS Project, for reporting purposes if the tool doesn't
provide the customized reporting you seek)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
S

Scoop

Scoop

Is there a reason you aren't using a tool designed specifically to manage
projects?  While it will certainly be possible to re-create the wheel and
invent an Access database to do this, why?!

(and if I recall correctly, it is possible to use Access to "see" recordsin
something like MS Project, for reporting purposes if the tool doesn't
provide the customized reporting you seek)

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -
Click to expand...

Well, that's certainly a good question.

First, the information I'm working with is much more specific than I
described. I used "projects" and "tasks" as generic terms to describe
the data I'm working with. Secondly, I'm hoping to do some things that
I'm not sure Project Management or other off the shelf applications
will do. The "projects" that I speak of have sets of criteria that
have to be met. Many of the criteria are either dependent on one
another or related to one another. Assuming I do use Access, I'll have
forms that will, for example, allow a user to choose update the status
of other tasks when updating one task.

That said, I hadn't really thought of the route you suggested so I'll
look into it to see if it will work. In the meantime, I'd still like
to figure out this table structure:)

I know my description is a bit vague but it's the best I can do...
 
J

Jeff Boyce

Scoop

Folks here are largely volunteers, and response with specific suggestions to
specific descriptions. As you say, your description is a bit vague.

The "right" table structure really depends on the specifics of what you have
and what you want to do. A generic description is likely to get you generic
suggestions.

If the terms "normalization" and "relational" aren't familiar, plan to spend
to some time coming up to speed on them. This is really the starting point
for using the relationally-oriented MS Access. "How" is not the first
question, "what" is!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Scoop

Is there a reason you aren't using a tool designed specifically to manage
projects? While it will certainly be possible to re-create the wheel and
invent an Access database to do this, why?!

(and if I recall correctly, it is possible to use Access to "see" records
in
something like MS Project, for reporting purposes if the tool doesn't
provide the customized reporting you seek)

Regards

Jeff Boyce
Microsoft Office/Access MVP







- Show quoted text -
Click to expand...

Well, that's certainly a good question.

First, the information I'm working with is much more specific than I
described. I used "projects" and "tasks" as generic terms to describe
the data I'm working with. Secondly, I'm hoping to do some things that
I'm not sure Project Management or other off the shelf applications
will do. The "projects" that I speak of have sets of criteria that
have to be met. Many of the criteria are either dependent on one
another or related to one another. Assuming I do use Access, I'll have
forms that will, for example, allow a user to choose update the status
of other tasks when updating one task.

That said, I hadn't really thought of the route you suggested so I'll
look into it to see if it will work. In the meantime, I'd still like
to figure out this table structure:)

I know my description is a bit vague but it's the best I can do...
 
J

John... Visio MVP

Steve said:
I provide help with Access applications for a very reasonable fee. If you
need help with your project, contact me at (e-mail address removed).
Click to expand...

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified experts who gladly help for free. Stevie is
not one of them. Niether highly qualified or an expert, just a basic con
artist.

John... Visio MVP
 
H

Howard Burgman

Scoop said:
I'm trying to put together a database and can't quite come up with a
table structure that accomplishes what I want.

I am basically trying to track projects and tasks associated with each
project. The difficulty I'm having is that there are different types
of projects and each has a pre-defined set of tasks that come from a
master table of tasks. I want to be able to, for any given project,
list all of the tasks that need to be performed and monitor the status
of each task. For the table that stores the status for the tasks, I
want to maintain referential integrity to ensure that a task cannot be
created for a project unless the task is associated with the
particular type of project.
Click to expand...
 
S

Scoop

Thanks, everyone! ..and thanks for the heads-up, John!

I think my description(s) made this sound more complicated or more
vague than it actually is. I am actually tracking Projects and Tasks,
I'm just calling them something different in my implementation.

"Normalization" and "Relational" are not foreign concepts to me. I've
designed a number of relational databases, I'm just stumped on this
one.

Let me try again, using was Steve posted as a starting point. The
table structure he proposed is almost exactly what I came up with but
there is something I can't seem to wrap my mind around.

I can't see how this model will enforce one of my main criteria.
Namely, that "Projects" have a pre-defined set of "Tasks" based on
what "Type" of Project they are. In other words, I don't want to be
able to add a record to "TblProjectTasks" unless the given "Type" of
project allows for it.
 
M

Michael Gramelspacher

Thanks, everyone! ..and thanks for the heads-up, John!

I think my description(s) made this sound more complicated or more
vague than it actually is. I am actually tracking Projects and Tasks,
I'm just calling them something different in my implementation.

"Normalization" and "Relational" are not foreign concepts to me. I've
designed a number of relational databases, I'm just stumped on this
one.

Let me try again, using was Steve posted as a starting point. The
table structure he proposed is almost exactly what I came up with but
there is something I can't seem to wrap my mind around.

I can't see how this model will enforce one of my main criteria.
Namely, that "Projects" have a pre-defined set of "Tasks" based on
what "Type" of Project they are. In other words, I don't want to be
able to add a record to "TblProjectTasks" unless the given "Type" of
project allows for it.
Click to expand...


CREATE TABLE Projects (
project_id LONG NOT NULL,
project_name TEXT(50) NOT NULL,
CONSTRAINT pk_Projects PRIMARY KEY (project_id)
);
CREATE TABLE Tasks (
task_id LONG NOT NULL,
task_name TEXT(50) NOT NULL,
CONSTRAINT pk_Tasks PRIMARY KEY (task_id)
);
CREATE TABLE ProjectsTypes (
project_type TEXT(20) NOT NULL,
CONSTRAINT pk_ProjectTypes PRIMARY KEY (project_type)
);
CREATE TABLE ProjectTypeTasks (
project_type TEXT(20) NOT NULL,
task_id LONG NOT NULL,
CONSTRAINT fk_ProjectsTypes_ProjectTypeTasks
FOREIGN KEY (project_type) REFERENCES
ProjectsTypes (project_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_Tasks_ProjectTypeTasks
FOREIGN KEY (task_id) REFERENCES
Tasks (task_id),
CONSTRAINT PrimaryKey PRIMARY KEY (project_type, task_id)
);
CREATE TABLE ProjectTasks (
project_id LONG NOT NULL,
project_type TEXT(50) NOT NULL,
task_id LONG NOT NULL,
start_date DATETIME NOT NULL,
stop_date DATETIME,
CONSTRAINT fk_Projects_ProjectTasks
FOREIGN KEY (project_id) REFERENCES
Projects (project_id),
CONSTRAINT fk_ProjectTypeTasks_ProjectTasks
FOREIGN KEY (project_type, task_id) REFERENCES
ProjectTypeTasks (project_type, task_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT pk_ProjectsTasks
PRIMARY KEY (project_id, task_id)
);

I think you will need to delete the
ON UPDATE CASCADE
ON DELETE CASCADE
if you run these in the SQL Window and recreate them in the Relationships window..
 
S

Scoop

Thanks, Michael!

I ran the queries without any trouble and I'm looking at it now. It'll
take me a little bit to wrap my mind around this one. I'll play around
with my data and report back.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do you combine subform data with mainform data? 2
Can I create a sub-table for one certain field? 6
Track Projects in Access via some type of Calendar control 4
For Each ... Next loop 4
Automatically number task on various levels 1
Adding/Updating Record in another Table 1
DATABASE DESIGN 9
Sharing Combo Box data with other Combo Box 3

Top