hi tmarsh,
My limited knowledge of this stuff is self tought, so bare with me. If i had noticed all the hard work MStef had put into fixing your database originally, i probably woudn't even have replied since I'm probably just making it more confusing to you.
One thing I've learned is that 'THERE IS ALWAYS MORE THAN ONE WAY OF DOING IT', however, setting up the tables is the most important thing and will carry over into everything else you do.
My thoughts on how I would set up your tables is based on the idea that each individual course only happens once, however, I suppose if each course was identifiable by a course number and could occur several times, it could look something like this:
TB_Course_Number
Course_Number
pk
CourseName
Description
TB_Course
Course_ID
pk
Course_Number
StartDate
EndDate
Venue
Trainer
TB_Staff
Staff_ID
pk
FName
LName
etc...
TB_Courses_Done
Staff_ID
pk
Course_ID
pk
PassFail
As you can see... just another way of doing it... in your
original tables you had duplicate course names in two different tables, which is always an indicator of poor table structure.
In this example you can see that I used Staff_ID and Course_ID as a composite key for the TB_Courses_Done table, but also included a field to show if the person passed or failed the course.
As for relationships, yes, just matching the words that I used for each table should give you a pretty good indicator which fields to 'relate' to each other.
But should the courses_done_id and staff_id in this table be composite?
Their is no Courses_Done_ID, the composite key taken from the two other tables IS the identification for that table. There is no need to create its own key since it's not possible for one person to take the same course, on the same date, more than one time.
Hope this helps,
Scott
