Rookie ? How would you structure this database?

[T Bennett]

I am not too sure how to go about this:

I'm creating a form in InfoPath 2003 SP1 for project tracking. I need a
database to store my data. I don't know XML (which seems ideal for
InfoPath). Therefore I'm working in Access 2003 SP2

The setup:
There are many different engineers. Each name is unique.
Each engineer works on various dates. Engineers may work on the same
dates.
Each date the engineer has one or more projects to work on.
Each project has steps, notes, and time associated to them.

I've set up three tables:
One with engineers (primary key) only.
One with dates (primary key) and engineers.
One with project (primary key), engineers, dates, steps, notes, and
time.

How do I set up the relationships?
How do I set up the forms?

I basically need a form (Engineers) with a subform (Dates) with a
sub-subform (Projects). I can't figure how to do this.

Todd

 

[Dirk Goldgar]

I am not too sure how to go about this:

I'm creating a form in InfoPath 2003 SP1 for project tracking. I
need a database to store my data. I don't know XML (which seems
ideal for InfoPath). Therefore I'm working in Access 2003 SP2

The setup:
There are many different engineers. Each name is unique.
Each engineer works on various dates. Engineers may work on the
same dates.
Each date the engineer has one or more projects to work on.
Each project has steps, notes, and time associated to
them.

I've set up three tables:
One with engineers (primary key) only.
One with dates (primary key) and engineers.
One with project (primary key), engineers, dates, steps, notes,
and time.

How do I set up the relationships?
How do I set up the forms?

I basically need a form (Engineers) with a subform (Dates) with a
sub-subform (Projects). I can't figure how to do this.

You don't seem to have enough tables to represent all the entities and
the relationships among them. Clearly you need a table for Engineers,
storing only information that applies to the engineer regardless of
project or date. And clearly you need a table for Projects, storing
only information that applies to the project regardless of engineer or
date. I gather you need a table of ProjectSteps, with each record
related to a one of the records in Projects, storing one record for each
step of a project. But for the rest of the data design, I'd need more
information before deciding what further tables are needed. Here are
some questions for you:

1. Is there information that is specific to a date alone, and not
related to what an engineer is doing on that date? It's not clear to me
that you actually need a table of Dates, as such.

2. Are Engineers assigned to Projects before work begins? (I'm assuming
that the purpose of this database is to record engineers' work on
projects. Correct me if I'm wrong.)

3. Do "date", "time", and "notes" apply only to the work an engineer
does on a specific step of a specific project? If not, what do they
apply to?

In addition to answering these questions, it would help a lot if you
posted a more extensive description of what the database is supposed to
represent, and what kind of questions it is intended to answer.

 

[T Bennett]

Sorry for the late response. I was home sick for a couple days. My answers
are below.

Here are some questions for you:

1. Is there information that is specific to a date alone, and not
related to what an engineer is doing on that date? It's not clear to me
that you actually need a table of Dates, as such.

The only reason for the date is to know what an engineered did that day.
However, many engineers work different projects on the same dates. Several
engineers work on the same projects as other engineers as well.

2. Are Engineers assigned to Projects before work begins? (I'm assuming
that the purpose of this database is to record engineers' work on
projects. Correct me if I'm wrong.)

Engineers may be assigned projects before work begins, but we are only
tracking actual work performed for each day.

3. Do "date", "time", and "notes" apply only to the work an engineer
does on a specific step of a specific project? If not, what do they
apply to?

"Time" (in hours) and "Notes" only apply to that project for that day, not
for the project in general.
The "Step" further defines the project that is being worked on. The
"project" is quite general (Non-Standard Work). The "step" narrows it down
(Engineering Change Order).
The "Date" is just to track work for a specific engineer for a specific
date.

In addition to answering these questions, it would help a lot if you
posted a more extensive description of what the database is supposed to
represent, and what kind of questions it is intended to answer.

My boss wants to know what an Engineer worked on each day. He is currently
using a goofy tool with these fields. They may not be the best titles.
There are "Projects" which are general categories. Projects is a bit
misleading. A better term might actually be "Category". Some examples
might be "Custom Project" and "Work at Another Division". After this, we
need to specify the "Step" in the project. Here are a couple examples:
"Project Meeting" and "Create Mechanical Model". Then we pick how many
hours we worked on that step that day. We can add a note or select a
previous note. Now that I say it. Notes might need a table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks Dirk,

Todd

 

[dbahooker]

btw, congrats on using a real db; i think that XML is crazy talk until
SQL 2005.. im just tired of using text fields in SQL Server 2000

-aaron

 

[Dirk Goldgar]

Sorry for the late response. I was home sick for a couple days. My
answers are below.




The only reason for the date is to know what an engineered did that
day. However, many engineers work different projects on the same
dates. Several engineers work on the same projects as other
engineers as well.


Engineers may be assigned projects before work begins, but we are only
tracking actual work performed for each day.


"Time" (in hours) and "Notes" only apply to that project for that
day, not for the project in general.
The "Step" further defines the project that is being worked on. The
"project" is quite general (Non-Standard Work). The "step" narrows
it down (Engineering Change Order).
The "Date" is just to track work for a specific engineer for a
specific date.


My boss wants to know what an Engineer worked on each day. He is
currently using a goofy tool with these fields. They may not be the
best titles. There are "Projects" which are general categories.
Projects is a bit misleading. A better term might actually be
"Category". Some examples might be "Custom Project" and "Work at
Another Division". After this, we need to specify the "Step" in the
project. Here are a couple examples: "Project Meeting" and "Create
Mechanical Model". Then we pick how many hours we worked on that
step that day. We can add a note or select a previous note. Now
that I say it. Notes might need a table.

It sounds like what you need is a table of "EngineerWork" (or some such
abstract name), that would have these fields

EngineerWork
-----------------
WorkDate (date/time)
EngineerID (foreign key to Engineers)
ProjStepID (foreign key to ProjectSteps)
WorkHours (number)
Notes (memo)

This table could use the three fields {WorkDate, EngineerID, ProjStepID}
as a compound primary key, or you could add an automumber EngineerWorkID
field for that purpose.

Note that the field [ProjStepID] uniquely determines the Project as well
as the ProjectStep, because I'm assuming that ProjStepID is the primary
key of a ProjectSteps table that includes the ProjectID, like this:

ProjectSteps
--------------
ProjStepID (autonumber)
ProjectID (foreign key to Projects)
StepDescription (text)

As you say, it could be that you need a separate table for notes, if you
want to work with (and query) multiple notes per EngineerWork record as
distinct entities.

So in addition to the above tables, you have

Engineers
----------
EngineerID (primary key, maybe autonumber)
<various fields to name and describe the engineer>

Projects
---------
ProjectID (primary key, maybe autonumber)
<various fields to name and describe the project>

If you don't need to formally assign engineers to projects, then those
may be all the tables you need to represent this problem space. If
there's a list of possible "steps" from which the project steps must be
chosen, you'd probably want another table for that.

I agree with you that the terms "Project" and "Step" don't seem to be
being used sensibly. It's up to you whether you want to -- and have the
authority to -- revise that terminology.

 

[T Bennett]

Dude, you rock! Thanks.

Todd