Primary Key is sometimes Null.

[Petemush]

This seems to be a monster problem for me but I have the nagging feeling I'm missing something obvious.

I have a number of tables in my database:

tblEmployees - Employee Details
tblCourses - Course Details
tblCourseVariables - Course Details which can change with different occurences of the course

tblEmployeeCourses - Details of courses an employee has been on.

My problem concerns the last table.

Currently the primary keys in tblEmployeeCourses are the Employee's ID and the Course Code. The other fields are Date of notification, status, Course Date, Times Cancelled, and Date of Refresher.

The employee needs to go on a course so a new record is entered with Status: "Needed"

When the Status is changed to "Booked On" a Course Date is entered"

When the course date arrives, the status is changed to "Completed". And if necessary, Date of Refresher is filled in.

The problem is, if a refresher is needed, this means the employee will be going on the same course again at a later date but this information will not be able to be entered since it would duplicate the primary key, "Course Code" and "Employee No" so really "Course Date" should be part of the primary key as well. But the course date is null when the record is first entered at least until the status is changed to Booked On so it can't be part of the primary key because it can't be null at any point!

Soooooo, any ideas on how I deal with this? Do I need to add another field to differentiate between records or is there another way?

Thanks for any help,

Cheers,

Pete

 

[r937]

instead of adding a date column to the primary key, add a "refresher number" -- starts at 0 and increments for every additional entry for that employee/course

rudy

http://rudy.ca/

 

[mikey69]

I think you should reconsider the need for a composite primary key. It sounds as if you haven't normalised the data into appropriate tables. What i think should happen is that employeeid & courseid are separate foreign keys in the table employeecourse, for which the primary key should be an autonumber of no other significance.

 

[r937]

"primary key of no other significance" is a hotly debated issue, causing as many problems as it purports to solve

i don't want to get into a religious war, though

one of the main drawbacks of a composite primary key is that you have to repeat all the columns as foreign keys in any tables which reference the primary key's table -- but this is unlikely in pete's example, so that drawback goes away

one of the main drawbacks of an "insignificant" primary key is that it adds an extra index to the table, on top of the one(s) you have to declare for the "real" primary key columns

but like i said, it's a hotly contested issue

usually you weigh the pros and cons in each specific situation, and in pete's, i don't think he needs it

by the way, i'd like to point out that pete's tables were very nicely normalized

rudy

 

[Petemush]

Cheers for both your suggestions, both of them sound like a good idea but what I've done the mean time is:

Made Date of Notification part of the primary key.

This makes sense since:

It has to be composite, notification for different courses may happen on the same day.
Notification for a refresher of a course will never be the same date as the original notification of that course. Therefore unique.

This seems to solve my problem without adding another field but I'd appreciate feedback about using this approach, in case it's not a great idea for a reason I can't see. In which case I'll try one of the two suggestions above.

And thanks for the compliment about my normalized tables rudy!

Cheers for your help,

Pete