ONLY LOOKING FOR HELP WITH DESIGN.......I'll DO THE PROGRAMMING

[BURL ives]

Situation:
(1) a company has 100 employees

(2) employees have various job titles (director/chemist/VP/technician
etc)

(3) there are 200 procedures in this company and each employee has a
subset of these procedures which they need to be trained on; their
title determines which procedures they are associated with and what
level of training they receive

(4) occasionally the procedures are updated; the updated procedure
creates a need to update the training. The suggested training for the
updated procedure is based on the magnitude of the change and is not
job title dependent





Problem
Want to query the database twice monthly; find procedures that have
changed since the last query, find who is affected by the changes and
ideally email those people all automatically....however to manually
trigger a list of those people affected by the updates would also be
fine.





Here is what I am thinking so far.......
a table of EMPLOYEE NAME and JOB TITLE
a table of JOB TITLE, PROCEDURE NAME,
a table of PROCEDURE NAME, REVISION, DATE and TRAINING TYPE


I am not sure I am on the right track with the Tables and I can't seem
to figure out what the relationships are between tables.

ANY HELP WOULD BE APPRECIATED..........
thank you

 

[John W. Vinson]

Situation:
(1) a company has 100 employees

(2) employees have various job titles (director/chemist/VP/technician
etc)

(3) there are 200 procedures in this company and each employee has a
subset of these procedures which they need to be trained on; their
title determines which procedures they are associated with and what
level of training they receive

(4) occasionally the procedures are updated; the updated procedure
creates a need to update the training. The suggested training for the
updated procedure is based on the magnitude of the change and is not
job title dependent





Problem
Want to query the database twice monthly; find procedures that have
changed since the last query, find who is affected by the changes and
ideally email those people all automatically....however to manually
trigger a list of those people affected by the updates would also be
fine.





Here is what I am thinking so far.......
a table of EMPLOYEE NAME and JOB TITLE
a table of JOB TITLE, PROCEDURE NAME,
a table of PROCEDURE NAME, REVISION, DATE and TRAINING TYPE

You're on the right track. You have some Many to Many relationships though:
each Employee must be trained on one or more Procedures, and each Procedure
will be studied by one or more Employees. You need *another table* to model
this relationship, with fields EmployeeID (you *MUST* have a unique, stable
primary key for each table; employee names are not unique and not stable, so
they don't qualify) as a link to the Employee table, and ProcedureID (ditto)
as a link to the Procedures table.


John W. Vinson [MVP]

 

[BURL ives]

You're on the right track. You have some Many to Many relationships though:
each Employee must be trained on one or more Procedures, and each Procedure
will be studied by one or more Employees. You need *another table* to model
this relationship, with fields EmployeeID (you *MUST* have a unique, stable
primary key for each table; employee names are not unique and not stable, so
they don't qualify) as a link to the Employee table, and ProcedureID (ditto)
as a link to the Procedures table.


John W. Vinson [MVP]

John, you really nailed that one.....I created the joining table for a
many to many relationship and the rest of the db is working .

How about another question.........

in the table that has PROCEDURE NAME, REVISION #, REVISION DATE and
TRAINING TYPE, what are some suggestions to identifying which records
in that table have been updated. What I'm trying to do is when a
procedure gets updated, a new record is added to this table. The new
record includes the identifiers about the procedure along with the
date of the revision and the new revision number. This may occur once
or twice a month for a particular procedure or it may not happen at
all. I wish to periodically query the database, determine which
procedures have been updated and based on the relationship with the
Employees table, I determine which employees are affected by the
revision and alert them.

So any suggestions how to identify which records have updated info?
For a given PROCEDURE, do I look at the last 2 revision records and
determine through difference, if the change occurred in the past 2
weeks for example?

thanks in advance