Foriegn key which could point to multiple tables?

    I'm developing an online medical terminology learning system for a university, and I have an interesting concept to model. I've come up with a solution and I basically want to know if it's bad form or not... I think I'm missing something obvious.

The course I'm working on now contains basic entities like multiple choice tests, flash cards, and lectures. These entities are ordered for a given course, say chapter 1 is lectures 1 and 2, then chapter 2 is a set of flash cards. There are also little contextual requirements, like you can't access chapter 2 until you have completed chapter 1, etc. Anyway, I've got all the necessary data in tables already, so it wouldn't be that hard to just hardcode each course with some code templates or something, but!

I want to implement it in such a way that I can create, edit and rearrange courses very easily without having to add/change code (or tables if I can help it). The only thing I've come up with is to represent the courses flow by contextual rulesets in the database. The problem is these various entities are in different tables, and I'm trying to define this relationship in a dynamic, forward-compatible way. Here is the jist of what I've come up with...

Say I have 3 tables for example course entities

Tests (TestID, TestName)
Lectures (LectureID, LectureName)
FlashCards (FlashCardID, FlashCardName)

Then I use a "context rule table"

ContextRules (ContextID, EntityOccurence, EntityType, EntityID)

EntityOccurence is an INT which just says at what point in the course a specific entity occurs (chapter 1, chapter 2, chapter 3). EntityType is an INT that tells what type it is (1=Test, 2=Lecture, etc) EntityID is the Primary KEY of the entity based on the EntityType (TestID, LectureID or FlashID, so on.).

My question is, is this bad form? It seems a little wacky as I've never seen a "one table to many table" relationship, but it's also very catchall. The way I've laid it out in my head I can easily encapsulate code so that I can add new types of tests/lectures/entities, as well as be able to rearrange courses without having to update any *existing* tables or code (I may have to add new modules and tables of course). Hmmm, am I missing something?

Mainly I'm just scared because my ERD tool doesn't have a button for this. :-)

Eric Received on Sun May 04 2003 - 23:22:18 CEST