Re: Foriegn key which could point to multiple tables?

The relationship you are trying to model is the "is a" relationship. You have three entities that all represent different variations of a single fundamential concept (which you are calling "entity"). The first problem you'll have to solve is defining what this fundamental concept really represents. The fact that you are using the word "entity" to refer to it tells me that you have not yet brought the nature of this entity into focus - that should be an essential task in resolving this design issue.

Once you get that worked out the rest of the pieces should fall in place neatly using the standard practive for modelling an "is a" relationship.

As an example, let's call your mystery entity, "Academic Activity". We can then say:

A Test "is an" Academic Activity.
A Lecture "is an" Academic Activity.
A Flash Card "is an" Academic Activity.

Please note that this is only an example - based on these statements, I'm not sure "Academic Activities" is really adequeque - but let's pretend it is.

You can then model the is-a relationship by having these table share a common primary key. Since your example uses surrogate keys, we'll stick with that stategy and define an integer surrogate key called ActivityID as the primary key for the AcadaemicActivities table. You then also use ActivityID as the primary key for the Tests, Lectures, and FlashCards tables. For these three tables, ActivityID is defined as both the primary key and a foreign key that references the AcademicActivity table.

A useful trick with is-a relationships is to put a type flag column in the "parent" table that defines which "child" table shares the ActivityID for each row. This column has exactly the same function as the EntityType column in your ContextRules table. In fact, I predict that once you effectively define the qualities of the "entity" table, you will find that the "entity" table subsumes the function of the ContextRules table.

The "is-a" relationship is a common relation between entities that is similar to inheritance in an object oriented programing language. This relationship should be a recognized relationship type in the ERD tool you're using. If not, get another tool!

Good luck,

Paul
http://www.pjpm.biz

"Eric Gibson" <aithien_at_SCREWSPAMearthlink.net> wrote in message news:ewfta.54174$ey1.4980500_at_newsread1.prod.itd.earthlink.net...
> Hey,
>
> 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 Mon May 05 2003 - 00:21:23 CEST