Database Design Advice - Periodical Safety Training 1

[bud31047]

I am working on a SQL Server database to track OSHA Safety and company mandated training compliance. I felt fairly good about it until I attempted to create my views. I would appreciate it if someone could take a look and let me know if I'm heading down the wrong path.

The training is conducted 13 times a year (each period). There are 100+ topics that we track. Each period we may train on multiple topics. We may also train select individuals on topics that are not part of a set schedule.

Here are the highlights of my table structure;

EmployeeTbl
----------
EmpID
EmpName

TopicTbl
---------
TopicID
TopicName

ScheduleTbl
----------
SchID
SchYear
SchPeriod
TopicID

TrainingTbl
---------
RecordID
EmpID
TrainingDate
TopicID


Thanks in advance for any advice,

 

[r937]

looks okay so far

you might want to ask further questions in forum183

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bud31047]

Thanks. I will ask my follow-on questions in the Programming forum.

 

[Thargy]

bud,

I'd first of all suggest that you adopt a different naming convention, and lose the ubiquitous 'tbl'. In a database, it's reasonable to assume that the thing you're dealing with is a table, unless indicated otherwise. So,

SELECT * FROM tblEmployee

becomes

SELECT * FROM EMPLOYEE

If there was a view on said table then you would write

SELECT * FROM V_EMPLOYEE

or something similar.

You also have made mention of neither integrity nor validity constraints, which are one of the most important things in any database design.

You should also declare your foreign keys, so that we can see what kind of relational structure you're attempting to create, and review how well it's normalised. You also have not revealed the closely guarded secret of the data types, so we can't see whether or not you're committing any design crimes, such as storing dates as strings.

so, would you like to let us in on your secrets?

Regards

T

 

[bud31047]

Thargy,
Thanks for the feedback. Obviously my questioning skills could use some work as well as my design skills.

Let me try this again.

I am not sure which way is best to display relationship information but I will take a swing at it.

All of the tables listed are complete for column's with the exception of dbo.Employee; I omitted address, hire date, etc. All columns listed are required (NOT NULL).

dbo.Employee
----------
GPID(PK) Int
LastName nvarchar
FirstName nvarchar

dbo.SafeTopics
---------
SafeTopicID(PK) Int
SafeTopicDesc nvarchar

dbo.SafeSchedule
----------
SafeScheduleID(PK) Int
YearNum(FK) Int
PeriodNum(FK) Int
SafeTopicID(FK) Int

dbo.Safe
---------
SafeId(PK) Int
SafeGPID(FK) Int
SafeDate Int
SafeTopicID(FK) Int

dbo.Years
---------
YearNum(PK) Int
YearText nchar

dbo.Periods
-----------
PeriodNum(PK) Int
PeriodText nvarchar

As I type this I can see that I need to work on my naming convention a bit. For example, "Employee" should have been named "Employees".

Is my approach to the problem workable? Would you have laid-out the tables differently to capture the data? If so, why?

Thanks again,

 

[Thargy]

bud,

thanks for the extra info. I'm an Oracle weenie myself, and you're obviously working with SQL Server, but I ought to be able to make some sense of what you're up to.

First of all, you need to start producing scripts, which when run will produce the tables and structure you need. This is much better than posting table structures, as we can then zoom in on a particular issue, easily isolate and correct it, ok?

So, as to naming, you can ditch the word "safe" from all your names. Since this will be in a database called "Safety" or some such, it is redundant.

So, I make the following statement, and offer it for your correction, so that we have a clear statement of requirement which is mutually agreed.

It is required to monitor and report on employees safety training, on a regular basis, at least every period. There are 13 periods in a calendar year, and they are defined as ?????.

Safety training courses consist of numerous topics, which are combined into a schedule. It is required to report on which topics are in which schedule. A schedule may contain one or more topics, and a topic may appear in one or more schedule.

If an employee attends every course in a schedule then they are deemed to have completed the course. Sometimes employees may be trained on an individual topic, instead of all topics in a schedule, and this too must be tracked and reported.

Many employees may attend many schedules of training spanning many periods and calendar years. Many employees may also attend many individual topics, which may occur in many calendar years.

It is required that the system be able to report on individual empolyees, topics and schedules. Reports may be required per period, employess, topic and/or schedule, sorted and ordered date and/or any of the above criteria.

Is that a reasonable statement of requirement bud?

Regards

T

 

[bud31047]

I created a table not listed above to get Period from SafeDate.
-------------------------------------------------------

CREATE DATABASE Safety
GO

USE SAFETY
CREATE TABLE Employees
( GPID int NOT NULL PRIMARY KEY,
FirstName nchar(20) NOT NULL,
Department nchar(20) NOT NULL)

INSERT INTO Employees (GPID, FirstName, Department)
VALUES (1,'Bob','Shipping')
INSERT INTO Employees (GPID, FirstName, Department)
VALUES (2,'Mary','Maintenance')

CREATE TABLE Periods
( PeriodNum int NOT NULL PRIMARY KEY,
PeriodText nchar(10) NOT NULL)

INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(1,'1')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(2,'2')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(3,'3')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(4,'4')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(5,'5')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(6,'6')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(7,'7')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(8,'8')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(9,'9')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(10,'10')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(11,'11')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(12,'12')
INSERT INTO Periods (PeriodNum, PeriodText)
VALUES(13,'13')

CREATE TABLE Years
( YearNum int NOT NULL PRIMARY KEY,
YearText nchar(10) NOT NULL)

INSERT INTO Years (YearNum, YearText)
VALUES (2010, '2010')
INSERT INTO Years (YearNum, YearText)
VALUES (2011, '2011')
INSERT INTO Years (YearNum, YearText)
VALUES (2012, '2012')
INSERT INTO Years (YearNum, YearText)
VALUES (2013, '2013')
INSERT INTO Years (YearNum, YearText)
VALUES (2014, '2014')
INSERT INTO Years (YearNum, YearText)
VALUES (2015, '2015')

CREATE TABLE Topics
( TopicID int NOT NULL PRIMARY KEY,
TopicDesc nvarchar(50) NOT NULL)

INSERT INTO Topics (TopicID, TopicDesc)
VALUES (1,'Forklift Safety')
INSERT INTO Topics (TopicID, TopicDesc)
VALUES( 2,'Hearing Conservation')
INSERT INTO Topics (TopicID, TopicDesc)
VALUES (3,'Ergonomics')
INSERT INTO Topics (TopicID, TopicDesc)
VALUES (4,'Fall Protection')

CREATE TABLE Schedule
( ScheduleID int NOT NULL PRIMARY KEY,
YearNum int NOT NULL,
PeriodNum int NOT NULL,
TopicID int NOT NULL,
FOREIGN KEY (YearNum) REFERENCES Years(YearNum),
FOREIGN KEY (PeriodNum) REFERENCES Periods(PeriodNum),
FOREIGN KEY (TopicID) REFERENCES Topics(TopicID))

INSERT INTO Schedule (ScheduleID, YearNum, PeriodNum, TopicID)
VALUES(1, 2011, 1, 2)
INSERT INTO Schedule (ScheduleID, YearNum, PeriodNum, TopicID)
VALUES(2, 2011, 2, 1)
INSERT INTO Schedule (ScheduleID, YearNum, PeriodNum, TopicID)
VALUES(3, 2011, 2, 3)
INSERT INTO Schedule (ScheduleID, YearNum, PeriodNum, TopicID)
VALUES(4, 2012, 1, 4)

CREATE TABLE Training
( TrainingID int NOT NULL PRIMARY KEY,
GPID int NOT NULL,
SafeDate datetime NOT NULL,
TopicID int NOT NULL,
FOREIGN KEY (GPID) REFERENCES Employees(GPID),
FOREIGN KEY (TopicID) REFERENCES Topics(TopicID))

INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (1, 1, '01/01/2011', 2)
INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (2, 2, '01/01/2011', 2)
INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (3, 1, '01/25/2011', 1)
INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (4, 1, '01/25/2011', 3)
INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (5, 2, '01/25/2011', 3)
INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (6, 1, '01/01/2012', 4)
INSERT INTO Training (TrainingID, GPID, SafeDate, TopicID)
VALUES (7, 2, '01/01/2012', 4)

CREATE TABLE Weeks
( WeekID int NOT NULL PRIMARY KEY,
Period int NOT NULL,
WkPd nchar(10) NOT NULL)

INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (1,1,'1x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (2,1,'1x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (3,1,'1x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (4,1,'1x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (5,2,'2x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (6,2,'2x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (7,2,'2x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (8,2,'2x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (9,3,'3x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (10,3,'3x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (11,3,'3x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (12,3,'3x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (13,4,'4x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (14,4,'4x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (15,4,'4x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (16,4,'4x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (17,5,'5x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (18,5,'5x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (19,5,'5x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (20,5,'5x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (21,6,'6x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (22,6,'6x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (23,6,'6x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (24,6,'6x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (25,7,'7x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (26,7,'7x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (27,7,'7x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (28,7,'7x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (29,8,'8x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (30,8,'8x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (31,8,'8x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (32,8,'8x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (33,9,'9x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (34,9,'9x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (35,9,'9x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (36,9,'9x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (37,10,'10x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (38,10,'10x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (39,10,'10x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (40,10,'10x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (41,11,'11x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (42,11,'11x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (43,11,'11x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (44,11,'11x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (45,12,'12x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (46,12,'12x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (47,12,'12x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (48,12,'12x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (49,13,'13x1')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (50,13,'13x2')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (51,13,'13x3')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (52,13,'13x4')
INSERT INTO Weeks (WeekID, Period, WkPd)
VALUES (53,13,'13x5')

GO

CREATE VIEW vwTraining
AS
SELECT dbo.Training.TrainingID, dbo.Training.GPID, dbo.Training.SafeDate, dbo.Training.TopicID, dbo.Weeks.Period, dbo.Weeks.WkPd,
YEAR(dbo.Training.SafeDate) AS TrainingYear
FROM dbo.Training INNER JOIN
dbo.Weeks ON { fn WEEK(dbo.Training.SafeDate) } = dbo.Weeks.WeekID

 

[Thargy]

bud,

thanks for posting the create scripts - that's much more informative.

I'm trying to download sql server express, so I can work directly with you, failing that, I'll do it in Oracle, and then we can do a translation exercise. I'll get back to you after I finish work today.


Regards

T

 

[bud31047]

Thanks for the help.

 

[Thargy]

Bud,

Having downloaded and installed sql server express, and then done battle with the productivity tools, I can finally do direct work on a database.

I've modified your TOPIC table slightly, and have created it with the script below. Note the use of an identity field, and the integrity constraints which are named - much easier than querying the data dictionary for them. The schedule table is simple - I'm goint to attempt to show you that you don't need the week and period tables.

--DROP TABLE TOPIC

CREATE TABLE TOPIC
(
TOPIC_ID INTEGER IDENTITY(1,1),
TOPIC_DESC NVARCHAR(50)
)

ALTER TABLE TOPIC ADD CONSTRAINT PK_TOPIC PRIMARY KEY (TOPIC_ID)
ALTER TABLE TOPIC ADD CONSTRAINT NN_TOPIC__DESC CHECK (TOPIC_DESC IS NOT NULL)
ALTER TABLE TOPIC ADD CONSTRAINT NWS_TOPIC__DESC CHECK (TOPIC_DESC = LTRIM(RTRIM((TOPIC_DESC))))
ALTER TABLE TOPIC ADD CONSTRAINT UQ_TOPIC__DESC UNIQUE (TOPIC_DESC)

INSERT INTO TOPIC (TOPIC_DESC) VALUES ('Forklift Safety')
INSERT INTO TOPIC (TOPIC_DESC) VALUES( 'Hearing Conservation')
INSERT INTO TOPIC (TOPIC_DESC) VALUES ('Ergonomics')
INSERT INTO TOPIC (TOPIC_DESC) VALUES ('Fall Protection')



--DROP TABLE SCHEDULE

CREATE TABLE SCHEDULE
(
SCHEDULE_ID INTEGER IDENTITY(1,1),
TOPIC_ID INTEGER,
DATE_OCCURRED DATETIME
)

ALTER TABLE SCHEDULE ADD CONSTRAINT PK_SCHEDULE PRIMARY KEY (SCHEDULE_ID)
ALTER TABLE SCHEDULE ADD CONSTRAINT NN_SCHEDULE__TOPIC_ID CHECK (TOPIC_ID IS NOT NULL)
ALTER TABLE SCHEDULE ADD CONSTRAINT NN_SCHEDULE__DATE_OCCURRED CHECK (DATE_OCCURRED IS NOT NULL)
ALTER TABLE SCHEDULE ADD CONSTRAINT FK_SCHEDULE__TOPIC FOREIGN KEY (TOPIC_ID) REFERENCES TOPIC(TOPIC_ID)

INSERT INTO SCHEDULE (TOPIC_ID,DATE_OCCURRED) VALUES (1,GETDATE())

Regards

T

 

[Thargy]

Bud,

you don't need the period and week tables, as they are calculable from the date information alone. Although untested, the view below implements the logic of 12 four week periods, followed by a thirteenth having five weeks. Run the script after the previous one posted and give it a whirl.

CREATE VIEW V_SCHEDULE_INFO
AS
SELECT S.DATE_OCCURRED,
       DATEPART(WEEK, S.DATE_OCCURRED) AS WEEK_OF_YEAR,
       T.TOPIC_DESC,
       CASE 
       WHEN (DATEPART(WEEK, S.DATE_OCCURRED) < 49 AND DATEPART(WEEK, S.DATE_OCCURRED)%4 = 0) THEN (DATEPART(WEEK, S.DATE_OCCURRED)/4) 
       WHEN (DATEPART(WEEK, S.DATE_OCCURRED) < 49 AND DATEPART(WEEK, S.DATE_OCCURRED)%4 <> 0) THEN (DATEPART(WEEK, S.DATE_OCCURRED)/4)+1
       ELSE 13
       END AS PERIOD
   FROM SCHEDULE S
 INNER JOIN TOPIC T ON S.TOPIC_ID = T.TOPIC_ID
 
 SELECT * FROM V_SCHEDULE_INFO

DATE_OCCURRED	        WEEK_OF_YEAR	TOPIC_DESC	PERIOD
2011-04-27 23:23:24.720	18	        Forklift Safety	5

As you can see from the date and time I used, it's now my bedtime. Let us resume battle tomorrow. Is it clear to you why you don't need week and period tables? You just store the relevant date and then calculate them.

Regards

T

 

[bud31047]

Thargy,

The code to extract the Period from the training date works great. Thanks for that. After an hour of tinkering with various functions I realized it would just be quicker to create the Weeks table.

I was unable to use your script to create and alter the tables. Possibly you made some changes to the database that are not reflected in your script.

One question. Are you suggesting to combine the Schedule and Training tables or were you just demonstrating how to get period from a training date?

 

[Thargy]

Bud,

whatever you do, don't create a week table, it's as pointless as creating a table and storing each day of the year in it, when you can just use GETDATE(). In a database you should normally never store calculated (or calculable) values, as they can go wrong. Always use the system to generate dates, and/or their associated information. Get rid of that week table! It may be quicker now, but it will cause you nightmares later on.

My changes to a database are always scripted. I ran them repeatedly in SQL Server Express 2008 (hence the commented drop statements) using the microsoft downloads for the database and sql manager environment. They were run from the query analyzer. When the scripts failed to run for you, what version were you using, and what error messages did you receive? Please post them.

I'm not sure about the schedule table. Does it contain a list of dates on which training courses are scheduled to occur, or is it storing an historic record of past training dates? You'll have to let me know your requirement before I can answer.

Over to you Bud.

Regards

T

 

[bud31047]

Could not drop object 'TOPICS' because it is referenced by a FOREIGN KEY constraint."

I am using SQL Server 2005 Express. That might explain it.

Here is a quick rundown of what I am trying to get.

We have 1300 employees where I work that we have to train each year; OSHA safety training, company mandated training, job specific training, re-certification, and etc. Some topics are required every year, some every two years, and some once a career. Right now I am concerned about the topics that have to be covered every year, the OSHA safety training.

My employer creates a training schedule at the beginning of the year that lists what topic ('s) will be covered each period. My job is to create an application to track and report which employees have not completed the required training.

My train of thought is, each record in the "Employee" table must have a related record in the "Training" table that corresponds with the Period, Year and Topic of each record in the "Schedule" table. We perform make-up training sessions for any employee that misses a class. The employee is considered trained on a topic if the period in which they are trained is equal to or greater than the period set out in the schedule.

Make sense?

I really appreciate the help. This project is testing my limited database skills to say the least.

 

[Thargy]

bud,

the reason for the script failing is that you appear to be running it in an already populated database. You must not run these scripts on your production system - run them in a copy first.

Use a blank database and try them out. The error you reported is that the table TOPICS can't be dropped because it is referenced by another - quite correct.

I was assuming that we were working in isolation here.
Please re-run these in a blank and let me know the outcome.

Regards

T

 

[bud31047]

I think I have the database good to go now.

One last thing before we wrap this up. I would appreciate it if you could get me pointed in the right direction to create a view that shows who has not had all of their safety training.

Thanks again,

 

[Thargy]

bud,

I thought that might be the case. However, I realised that you might hit some snags, and anticipated precisely what you asked for.
Below is my solution, which provides all that you need in a normalised fashion, without redundant tables. Have a look at the tables, and particularly note the MAX_ALLOWED_INTERVAL in training record. it enables calculation of overdue and due dates for training. Note that your employers schedule of dates is a red herring, it has NOTHING to do with the design. The scheduled dates for activities are all well and good, but for your purposes, what counts is when training last occurred (or indeed, if it took place), and what training is due and overdue.

These scripts should be run in order in a blank database.


Use SQL server to generate a diagram, and have a look at the referential integrity, and see if you can follow it.

--DROP TABLE DEPARTMENT

CREATE TABLE DEPARTMENT
(
DEPARTMENT_ID INTEGER IDENTITY (1,1),
DEPT_NAME NVARCHAR(20)
)

ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPARTMENT_ID)
ALTER TABLE DEPARTMENT ADD CONSTRAINT NN_DEPARTMENT__DEPT_NAME CHECK (DEPT_NAME IS NOT NULL)
--No leading or trailing white spaces in text
ALTER TABLE DEPARTMENT ADD CONSTRAINT NWS_DEPARTMENT__DEPT_NAME CHECK (DEPT_NAME = LTRIM(RTRIM(DEPT_NAME)))
ALTER TABLE DEPARTMENT ADD CONSTRAINT UQ_DEPARTMENT__DEPT_NAME UNIQUE (DEPT_NAME)

SET IDENTITY_INSERT DEPARTMENT ON
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPT_NAME) VALUES (1,'Maintenance')
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPT_NAME) VALUES (2,'Shipping')
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPT_NAME) VALUES (3,'Packaging')
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPT_NAME) VALUES (4,'Purchasing')
SET IDENTITY_INSERT DEPARTMENT OFF

--DROP TABLE EMPLOYEE

CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID INTEGER IDENTITY(1,1),
FIRST_NAME NVARCHAR(20),
FAMILY_NAME NVARCHAR(20),
DEPARTMENT_ID INTEGER
)

ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
ALTER TABLE EMPLOYEE ADD CONSTRAINT NN_EMPLOYEE__FIRST_NAME CHECK (FIRST_NAME IS NOT NULL)
ALTER TABLE EMPLOYEE ADD CONSTRAINT NN_EMPLOYEE__FAMILY_NAME CHECK (FAMILY_NAME IS NOT NULL)
ALTER TABLE EMPLOYEE ADD CONSTRAINT NN_EMPLOYEE__DEPT_ID CHECK (DEPARTMENT_ID IS NOT NULL)
ALTER TABLE EMPLOYEE ADD CONSTRAINT NWS_EMPLOYEE__FAMILY_NAME CHECK (FAMILY_NAME = LTRIM(RTRIM(FAMILY_NAME)))
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE__DEPARTMENT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)

SET IDENTITY_INSERT EMPLOYEE ON
INSERT INTO EMPLOYEE (EMPLOYEE_ID,FIRST_NAME,FAMILY_NAME,DEPARTMENT_ID) VALUES (1,'Bob','Marley',4)
INSERT INTO EMPLOYEE (EMPLOYEE_ID,FIRST_NAME,FAMILY_NAME,DEPARTMENT_ID) VALUES (2,'Mary','Jones',2)
SET IDENTITY_INSERT EMPLOYEE OFF

--To see employees and their departments use
--SELECT E.FIRST_NAME,
--       E.FAMILY_NAME,
--       D.DEPT_NAME
--  FROM EMPLOYEE AS E
-- INNER JOIN DEPARTMENT AS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

--Reference data for training type

DROP TABLE TRAINING_TYPE

CREATE TABLE TRAINING_TYPE
(
TRAINING_TYPE_ID INTEGER IDENTITY (1,1),
TYPE_DESC NVARCHAR(20)
)

ALTER TABLE TRAINING_TYPE ADD CONSTRAINT PK_TRAINING_TYPE PRIMARY KEY (TRAINING_TYPE_ID)
ALTER TABLE TRAINING_TYPE ADD CONSTRAINT NN_TRAINING_TYPE__TYPE_DESC CHECK (TYPE_DESC IS NOT NULL)
--No leading or trailing white spaces in text
ALTER TABLE TRAINING_TYPE ADD CONSTRAINT NWS_TRAINING_TYPE__TYPE_DESC CHECK (TYPE_DESC = LTRIM(RTRIM(TYPE_DESC)))
ALTER TABLE TRAINING_TYPE ADD CONSTRAINT UQ_TRAINING_TYPE__TYPE_DESC UNIQUE (TYPE_DESC)

SET IDENTITY_INSERT TRAINING_TYPE ON
INSERT INTO TRAINING_TYPE (TRAINING_TYPE_ID,TYPE_DESC) VALUES (1,'OHSA')
INSERT INTO TRAINING_TYPE (TRAINING_TYPE_ID,TYPE_DESC) VALUES (2,'Company_mandated')
INSERT INTO TRAINING_TYPE (TRAINING_TYPE_ID,TYPE_DESC) VALUES (3,'Job-specific')
INSERT INTO TRAINING_TYPE (TRAINING_TYPE_ID,TYPE_DESC) VALUES (4,'Recertification')
SET IDENTITY_INSERT TRAINING_TYPE OFF


--Reference data for TRAINING

--DROP TABLE TRAINING

CREATE TABLE TRAINING
(
TRAINING_ID          INTEGER IDENTITY (1,1),
TRAINING_DESC        NVARCHAR(20),
ONLY_NEEDED_ONCE     BIT,
MAX_ALLOWED_INTERVAL TINYINT,
TRAINING_TYPE_ID     INTEGER
)

ALTER TABLE TRAINING ADD CONSTRAINT PK_TRAINING PRIMARY KEY (TRAINING_ID)
ALTER TABLE TRAINING ADD CONSTRAINT NN_TRAINING__TRAINING_DESC CHECK (TRAINING_DESC IS NOT NULL)
--No leading or trailing white spaces in text
ALTER TABLE TRAINING ADD CONSTRAINT NWS_TRAINING__TRAINING_DESC CHECK (TRAINING_DESC = LTRIM(RTRIM(TRAINING_DESC)))
ALTER TABLE TRAINING ADD CONSTRAINT UQ_TRAINING__TRAINING_DESC UNIQUE (TRAINING_DESC)
ALTER TABLE TRAINING ADD CONSTRAINT NN_TRAINING__ONLY_NEEDED_ONCE CHECK (ONLY_NEEDED_ONCE IS NOT NULL)
ALTER TABLE TRAINING ADD CONSTRAINT NO_INTERVAL_IF_ONLY_NEEDED_ONCE 
                                    CHECK (
                                           (ONLY_NEEDED_ONCE = 0 AND MAX_ALLOWED_INTERVAL IS NOT NULL)
                                           OR
                                           (ONLY_NEEDED_ONCE = 1 AND MAX_ALLOWED_INTERVAL IS NULL)
                                           )
ALTER TABLE TRAINING ADD CONSTRAINT ONLY_RECORD_PAST_EVENTS CHECK (MAX_ALLOWED_INTERVAL > 0)
ALTER TABLE TRAINING ADD CONSTRAINT NN_TRAINING__TRAINING_TYPE CHECK (TRAINING_TYPE_ID IS NOT NULL)
ALTER TABLE TRAINING ADD CONSTRAINT FK_TRAINING__TRAINING_TYPE FOREIGN KEY (TRAINING_TYPE_ID) REFERENCES TRAINING_TYPE(TRAINING_TYPE_ID)


SET IDENTITY_INSERT TRAINING ON
INSERT INTO TRAINING (TRAINING_ID,TRAINING_DESC,ONLY_NEEDED_ONCE,MAX_ALLOWED_INTERVAL,TRAINING_TYPE_ID) VALUES (1,'Forklift Safety',0,12,1)
INSERT INTO TRAINING (TRAINING_ID,TRAINING_DESC,ONLY_NEEDED_ONCE,MAX_ALLOWED_INTERVAL,TRAINING_TYPE_ID) VALUES (2,'Hearing Conservation',1,NULL,2)
INSERT INTO TRAINING (TRAINING_ID,TRAINING_DESC,ONLY_NEEDED_ONCE,MAX_ALLOWED_INTERVAL,TRAINING_TYPE_ID) VALUES (3,'Ergonomics',0,24,3)
INSERT INTO TRAINING (TRAINING_ID,TRAINING_DESC,ONLY_NEEDED_ONCE,MAX_ALLOWED_INTERVAL,TRAINING_TYPE_ID) VALUES (4,'Fall Protection',0,24,4)
SET IDENTITY_INSERT TRAINING OFF

--DROP TABLE TRAINING_RECORD

CREATE TABLE TRAINING_RECORD
(
TRAINING_RECORD_ID INTEGER IDENTITY(1,1),
EMPLOYEE_ID INTEGER,
TRAINING_ID INTEGER,
DATE_LAST_ATTENDED DATETIME,
PERIOD AS
	(
	CASE
	WHEN (DATEPART(WEEK, DATE_LAST_ATTENDED) < 49 AND DATEPART(WEEK, DATE_LAST_ATTENDED)%4 = 0) THEN (DATEPART(WEEK, DATE_LAST_ATTENDED)/4)
	WHEN (DATEPART(WEEK, DATE_LAST_ATTENDED) < 49 AND DATEPART(WEEK, DATE_LAST_ATTENDED)%4 <> 0) THEN (DATEPART(WEEK,DATE_LAST_ATTENDED)/4)+1
	ELSE 13
	END
	),
WEEK_OF_YEAR AS DATEPART(WEEK, DATE_LAST_ATTENDED)
)

ALTER TABLE TRAINING_RECORD ADD CONSTRAINT FK_TRAINING_RECORD__EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)
ALTER TABLE TRAINING_RECORD ADD CONSTRAINT FK_TRAINING_RECORD__TRAINING FOREIGN KEY (TRAINING_ID) REFERENCES TRAINING(TRAINING_ID)

SET IDENTITY_INSERT TRAINING_RECORD ON
INSERT INTO TRAINING_RECORD (TRAINING_RECORD_ID,EMPLOYEE_ID,TRAINING_ID, DATE_LAST_ATTENDED) VALUES (1,1,2,GETDATE())
INSERT INTO TRAINING_RECORD (TRAINING_RECORD_ID,EMPLOYEE_ID,TRAINING_ID, DATE_LAST_ATTENDED) VALUES (2,2,3,GETDATE())
SET IDENTITY_INSERT TRAINING_RECORD OFF


--DROP VIEW V_TRAINING_INFO
--GO

CREATE VIEW V_TRAINING_INFO
AS 
SELECT TR.TRAINING_RECORD_ID,
       E.FIRST_NAME,
       E.FAMILY_NAME,
       D.DEPT_NAME,
       T.TRAINING_DESC,
       T.ONLY_NEEDED_ONCE,
       T.MAX_ALLOWED_INTERVAL,
       TR.DATE_LAST_ATTENDED,
       DATEADD(M,MAX_ALLOWED_INTERVAL,TR.DATE_LAST_ATTENDED) AS DATE_NEXT_DUE,
       TR.PERIOD,
       TR.WEEK_OF_YEAR
  FROM TRAINING_RECORD AS TR
 INNER JOIN EMPLOYEE   AS E ON TR.EMPLOYEE_ID  = E.EMPLOYEE_ID
 INNER JOIN DEPARTMENT AS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
 INNER JOIN TRAINING   AS T ON TR.TRAINING_ID  = T.TRAINING_ID

I suspect that you may have fallen into the classic trap of doing something fast, just to make it work. Then when calculations are needed, the design (or lack thereof) makes it tough.

Can you let me know of anything you don't understand. Note that in the training record table I used calculated fields for period and week of year. You absolutely MUST drop those tables (if you've still got them).

Regards

T

 

[bud31047]

Thargy,

This is good stuff and everything is easily understandable. I really like your use of computed columns to get the Period and Week. Your solution is much simpler than the one I came up with using the Schedule table as part of the equation. I am sure I can make your approach work for us.

You have been extremely helpful and patient. I appreciate it.

 

[Thargy]

The schedule is just that, a schedule.

Bear in mind that it is just a plan, and you are required to monitor past events. Also, note that the period is measured in months, so that you can give a warning along the lines of "In two months your certification in XYZ will expire, please book training immediately". It also caters for things which are required at periods of less than a year.

Good luck, and let me know if you have any further problems.

Regards

T