Table Design: Tests required for some but not all. Where to tell thedifference.

[GoCoogs]

I am building a database program that is tracking the results of
various activities from employees in different departments. The sticky
point is that all departments are not required to complete each
activity.

I have a staff table which is a list of staff members which includes a
reference to the department table which is a list of all departments.

I also have an activity table that contains the information for that
activity. I thought of adding a DeptReq field which would be a list of
the departments for which the activity was mandatory. This way when I
do a query for all those in the organization who were supposed to
complete the activity but haven't, I won't include those who were not
required to complete the activity. The only way I could think to do
this was comma separated list. This seems a bit clunky and I not
looking forward to coding the logic. Does anyone have better
suggestions?

Staff (StaffID; Name; Dept)
22, Jim; 2
23, Maggie; 3
24, Joseph; 1

Department (DeptID; DeptName)
1; Accounting
2; Operations
3; Shipping

Activitiy (ActID; ActivitName; DeptReq)
1; Safety Training; 2,3
2; Company Inservice; 2,3
3; Industry Conference; 1,2

Results (StaffID, Activity ID, Result)
22; 1; Pass
22; 3; Pass
23; 2; Pass

Now I would want to run a query that would return a list of those
responsible for the company inservice who did not complete it. I would
not want to include Joseph who was not responsible for attending but
would want to see Jim who was but does not have a result in the
Results table.


Thanks for your help.

 

[Michael Gramelspacher]

I am building a database program that is tracking the results of
various activities from employees in different departments. The sticky
point is that all departments are not required to complete each
activity.

I have a staff table which is a list of staff members which includes a
reference to the department table which is a list of all departments.

I also have an activity table that contains the information for that
activity. I thought of adding a DeptReq field which would be a list of
the departments for which the activity was mandatory. This way when I
do a query for all those in the organization who were supposed to
complete the activity but haven't, I won't include those who were not
required to complete the activity. The only way I could think to do
this was comma separated list. This seems a bit clunky and I not
looking forward to coding the logic. Does anyone have better
suggestions?

Staff (StaffID; Name; Dept)
22, Jim; 2
23, Maggie; 3
24, Joseph; 1

Department (DeptID; DeptName)
1; Accounting
2; Operations
3; Shipping

Activitiy (ActID; ActivitName; DeptReq)
1; Safety Training; 2,3
2; Company Inservice; 2,3
3; Industry Conference; 1,2

Results (StaffID, Activity ID, Result)
22; 1; Pass
22; 3; Pass
23; 2; Pass

Now I would want to run a query that would return a list of those
responsible for the company inservice who did not complete it. I would
not want to include Joseph who was not responsible for attending but
would want to see Jim who was but does not have a result in the
Results table.


Thanks for your help.

I might go with something like this:


CREATE TABLE Staff (
emp_nbr IDENTITY (1,1) NOT NULL,
first_name VARCHAR (30) NOT NULL,
last_name VARCHAR (30) NOT NULL,
PRIMARY KEY (emp_nbr));

CREATE TABLE Departments
(dept_nbr IDENTITY (1,1) NOT NULL PRIMARY KEY,
dept_name VARCHAR (50) NOT NULL UNIQUE);

CREATE TABLE Activities
(activity_nbr IDENTITY (1,1) NOT NULL PRIMARY KEY,
activity_title VARCHAR (100) NOT NULL UNIQUE,
refresh_months INTEGER NULL);

CREATE TABLE StaffActivities
(staff_nbr LONG NOT NULL
REFERENCES Staff (staff_nbr),
activity_nbr LONG NOT NULL
REFERENCES Activities (activity_nbr),
completion_date DATETIME NOT NULL,
PRIMARY KEY (staff_nbr, activity_nbr,completion_date));

CREATE TABLE DepartmentActivities
(dept_nbr LONG NOT NULL
REFERENCES Departments (dept_nbr),
activity_nbr LONG NOT NULL
REFERENCES Activities (activity_nbr),
PRIMARY KEY (dept_nbr, activity_nbr));

CREATE TABLE StaffAssignments
(staff_nbr LONG NOT NULL
REFERENCES Staff (staff_nbr),
dept_nbr LONG NOT NULL
REFERENCES Departments (dept_nbr),
start_date DATETIME NOT NULL,
stop_date DATETIME NULL,
PRIMARY KEY (staff_nbr, dept_nbr,start_date));

 

[Fred]

One idea:

The relationship between Departments and Courses is "many to many" and so
the usual practices would dictate a junction table where it has a record for
each instance of an activity being required for a department. I think that,
after such an addition, your structure would support whatever you want to do,
including the querying that you discussed.

Another way to look at the same idea is to have a 2 main tables, "people"
and "instances of a required activity for a department" with activities and
departments being just lookup tables for the latter main table.