Data Model

[Danth6961]

Good afternoon everyone,

I am trying to figure out the best solution for the following database. Any comments would be appreciated.

The product will track recurring training.

The part that I need suggestions is this.

For simplicity, let's say the org has 20 employees and there are 20 training classes.

Givens:

(1) All employees will have to do some of the classes.
(2) Some clasess will be one time only
(3) Some classes will require reccurence.

I really believe I need just one more table but for the life me can figure out how to best implement it. Taking normalization into account.

The table that holds the required classes for for each employee.

I've attached a pic of the data model I currently have.

Thanks in adv.

[griswolf]

That would be a join table that connects employees to courses, many to many. It might also have secondary information such as 'must finish by' or 'number of attempts allowed' ...

Minimally:

table tblEmployeeRequiredCourses (
  employee_id integer foreign key references tblEmployee.lngEmployeeId,
  course_id integer foreign key references tblCourse.lngCourseID
)

You may have external requirements, so take these next suggestions as "generally a good idea, but not required":

• Not all databases and OSs support camelCase, so it is wise to use underbar_separated names
• If you do use camelCase identifiers, be consistent: someId is better than someID : If you have to stop and think about how to case an "obvious" identifier, you lose, so consistency is efficient. Also, if, for instance "ID" turns up in the middle of the identifier, it will visually parse better if spelled "Id".
• Hungarian notation for types is ugly and at best of no particular value: The code documents the type. (At worst, it causes brittle maintenance when types need to change) Look here, for a well reasoned discussion on times when it does have value, and discusses how it came to be so widely misused.

[Danth6961]

First let me say thank you for the reply.

Is it really that simple? I had that table but it seemed wrong to me.

Sorry first database.

p.s. will make the suggetsed changes.

[Danth6961]

Here is a capture of the new data model.

Anyone care to comment.

This is my first attempt at db desin

[griswolf]

pk_required_course_id is superfluous: This is a join table, so it will never need its own id. Its primary key is the pair of foreign keys (so an employee cannot be required to take the same course more than once)

nit: course_description is misspelled

Issue to consider: Can an employee have more than one grade? More than one specialty? More than one work center? Be more than one type? In each case:

• If the answer is "just one" (and the data is small) then it is as well to hold the data directly in the employee table rather than holding a foreign key. This only loses space if the data is larger than the size of a foreign key. Many databases provide an enum type that allows the best of both worlds: Value is stored as integer, displayed as string.
• If the answer is "just one" but the the data is more interesting (maybe a specialty or type has a name, a description and maybe other stuff) then it is good to have a foreign key directly in the employee table as you have done.
• If the answer is "can be multiple" (for instance, I could have specialty rating for more than one machine, or I could work part time from one office and part time from another) then you need to have a join table for those things, similar to the one we have been discussing; and as with that table, there does not need to be an id column, just the two foreign keys.

[Danth6961]

1. fixed - thanks

2. This fullfills my need as some of those tables are not complete. Plus things like workcenter names change regularly (believe it or not/we always seem to be re-org'ing). "If the answer is "just one" but the the data is more interesting (maybe a specialty or type has a name, a description and maybe other stuff) then it is good to have a foreign key directly in the employee table as you have done."

3. I need to be able to show:

(a) Has the employee taken all the required courses.
(b) Is the employee current on all thier required courses. (not forgetting some of these courses are annual requirements and some are on time only)

Will this be best achieved through sql querries or am I missing needed table elements?

Would this also mean that "tblCourse.Transcript.pk_course_transcript_id" is not needed

Again thanks for thaking the time to assist.

[griswolf]

If there is an annual course requirement, then you have to do one of two things:

1. add a date (or datetime) column `deadline_date` or some such in the tblEmployeeRequiredCourse table. This date will become part of the primary key
2. give the annually required courses new entries in tblCourse, one for each year

Option 1 is good because it gives you a handle on something that is likely to be a requested later, and because it allows you to have required courses that must be done for periods other than yearly.
Option 2 is good because even annual courses change a bit from time to time (though maybe not every year). In order to be able to SELECT all the times that an employee has taken 'the same course', though, with option 2 you will need to be sure there is a key or index on a column that does not change over the years.

For currency on required courses, you need option 1 in any case.

Would this also mean that "tblCourse.Transcript.pk_course_transcript_id" is not needed?
You mean tblCourseTranscript.pk_course_transcript_id (one dot, not two). It depends on whether the course is an annually required one. You could avoid it by referencing both the course id and the individual's deadline column, but the SELECT would be a little bit tricky. In this case, I'd recommend keeping it (but don't tell my databases prof) ;)

Will this be best achieved through sql querries or am I missing needed table elements?
Yes, eventually the rubber meets the road at the SELECT statements. I think that the system you have is at least close to right. I'm still concerned about one issue: I have a feeling that is not necessarily a reality for this system that you need multiple specialty codes per employee. You will know better.

One more nit: table names are just as much identifiers as column names, so if you are going with my suggestion to lose camelCase names, you should change those too. This is not likely to ever be needed on your system, but since I'm a cross platform developer, I've gotten in the habit. (Both MySQL and Postgresql on OS/X and, I think, Windows have such issues, but of course slightly differently)

[Danth6961]

I have two fields in tbl_course

1. course_require_once (bln true/false)

- This will track if the course is a one time requirement only

2. course_qualification_length (int/days)

- This tracks how often the course needs to be repeated ( i.e. 12,18,24 months)

From what I think your saying this information would be better stored in tbl_employee_required_course.

Don't get hung up on the specialty code table. This tracks the members military specialty code. in the army it would be called thier MOS. Anyway, on occasion these change. For example one day the members specialty code is 21V the next day it's 21F. It happens.

So I really think if I can nail down these 3 tables I will be rockin-n-rollin'

[griswolf]

If the course must be repeated because of the course spec, then it should be in the course table. If it must be repeated because of the employee condition (passed low means repeat in 6 months, passed high means repeat in 2 years) then it needs to be in a join table between employee and course, and can live in the one you already have. If there are both conditions, then it needs to be in both places, and the select statement gets hairy(er); or you can keep the 'constant' ones also in the join table, though it isn't quite normal form.

Yes you should use a count of days for the interval that tracks how often the test must be taken. I'm pretty sure all databases will allow you do do date arithmetic with dates and days. Think about leap years, though.

[Danth6961]

Thank you very much for all your assistance.