looks okay so far

you might want to ask further questions in forum183: Microsoft SQL Server: Programming

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

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

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,

If there was a view on said table then you would write
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

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,


 

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

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

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

Thanks for the help.

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.

Regards

T

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.


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

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?
 

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

"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.
 

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

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,

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.


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

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.

 

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