Recursive Relationship Review

[lespaul]

I'm trying to implement a recursive relationship for an internal Training Tracking database and I've confused myself.

I the table to track individual courses AND the course tracks designed by our training division. So let's say that we have these courses available.

Introduction to Excel 1
Introduction to Excel 2
Introduction to Word 1
Introduction to Word 2

I need to be able to enforce pre-requisites (Excel 1 before Excel 2) and allow the user to create a track of courses. So the training coordinator could create a Word Track that would consist of Word 1 and Word 2, an Excel Track that consists of Excel 1 and Excel 2 and an Office Track that contains all 4 courses.

I had considered:

tblCourses
CourseID
CourseName
Renewable
RenewPeriod
Classification
NumOfDays

tblTracks
TrackID (FK to tblCourses.CourseID)
ClassID (FK to tblCourses.CourseID)
Sequence

tblPreReqs
CourseID (FK to tblCourses.CourseID)       }
PreReqCourseID (FK to tblCourses.CourseID) } Composite PK

[tt]
tblCourses
CourseID CourseName Renew RenewPeriod Classification NumOfDays
1 Introduction to Word 1 N IT Class 1
2 Introduction to Word 2 N IT Class 1
3 Introduction to Excel 1 N IT Class 1
4 Introduction to Excel 2 N IT Class 1
5 Word Certification N IT Track 2
6 Excel Certification N IT Track 2
7 Office Certification N IT Track 4

tblTracks
TrackID ClassID Sequence
5 1 1
5 2 2
6 3 1
6 4 2
7 1 1
7 2 2
7 3 3
7 4 4
[/tt]


Does that look reasonable?

Thanks for looking!

Les

 

[AlexCuse]

That's how I would have done it.

ClassID and Sequence are interchangeable in your example, but I think I know you well enough to presume this will not always be the case ;-)


Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

Ok, good. I thought I had it screwy, but seems to have worked out ok.

Thanks for looking!

les