Re: Trouble with foreign keys

[Peter Brawley]

If I understand you correctly, you have ...

user( userid )
screencategory( screencatid, ... )
screen( screenid, screencatid (ref screencategpry)... )
button ( buttonid, name, screenid (ref screen), ... )
event( eventid, userid (ref user), screenid (ref screen), buttonid ref button), ... )
lesson(lessonid,screenid(ref screen)...)
exam(examid, lessonid(ref lesson)...)
question(questionid, examid (ref exam), ...)

FKs are for parents. Trying to model grandparents with them usually fails for the reason you've discovered: "after I answer an explanation follows. So even if the question ID is the same, the screen ID changes", so the event table needs to lose its screenid ref, freeing screenid to change as the user clicks through the screen hierarchy.

And for similar reasons don't you also need this?

answer( answerid, questiond (ref question), ... )




,