Question Should I NOT enforce referential integrity in this case?

[cereo]

I hope someone can help me solve this puzzle:
I am designing a database that will link documents to training events on those documents and the employees present at the training events. The employees needing the training is determined by the employee’s job description.

Tables: (jt represents a junction table)
tbl_Employees
jt_Emp_JD each employee has many job descriptions
tbl_JobDescriptions
jt_JD_Doc each job description has many associated documents
tbl_Documents
jt_Doc_TE each document will have many associated trainings
tbl_TrainingEvents
jt_TE_Emp each training event will have many employees present

The documents are updated periodically, resulting in a change in the revision number (a field in tbl_Documents).
The employees are required to have training on the current documents, but records of past training must also be kept.
My question is this: How do I set up the referential integrity so that a training event can only be based on a document currently in tbl_Documents, but once a document is updated, it does not update the records of past training events so that the record in jt_TE_Emp will show that they were trained on the previous version but not yet on the current version?
I had thought about leaving the old document in and adding the revision in as a new document and enforcing referential integrity, but then I couldn’t figure out how to update the jt_JD_Doc to reference the new document revision (if I just update the doc instead of replacing it and cascade the update to that table, then voila)

Any advice?

 

[spikepl]

Consider splitting the document in a parent and children.

The parent carries the title, and some document number, and you link to that, and in this way the conceptual dependence between the document and , say, job descitpiton is established and preserved.

Each child is then each new issue of the document id'ed by the parent - version number, issue date, perhaps validity etc.

 

[cereo]

Thanks, spikepl.
I'm assuming you mean for me to enforce RI all the way around, yes?
I'm not sure how that will help with the problem of building a query that will yield a result like, Employee X with Job Description Y had training on Document 1A but not Document 1B.
Am I just being thick headed?

 

[spikepl]

Bluntly put, yes

This structure would allow you to stipulate what training is necessary - between a given JobDescription and SOP/WI. This is independent of document versions. An employee is trained on specific versions of a document. The need, as embedded in tha data structure, is then no longer affected by issuance of a new version, which was your stated problem.

 

[cereo]

Great! I'll give that a whirl. Thanks!

 

[Pat Hartman]

jt_TE_Emp should be jt_Emp_Doc so you can relate the employee to the specific document he trained on and the junction table should include the date and grade if one is issued.

 

[cereo]

Pat, thanks for your reply. tbl_TrainingEvents has a field for the Document, will that do the same thing, i.e. let me relate the employee's training to the document? That's where I was headed, but you might be seeing something I am missing.

 

[Pat Hartman]

The junction table needs to point to whatever the employee was actually trained on. Perhaps you need a child table to hold the revisions and the junction table would link to that. Do you have a document table that describes a document and a child table with a record for each revision and the junction table points to the revision so you can tell who was trained in which version.