Re: Foriegn key which could point to multiple tables?

"Paul Keister" <sp_guard_info_at_sp_guard_pjpm.biz> wrote in message news:Dngta.207$H37.31688070_at_newssvr15.news.prodigy.com...
> 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.
>

Thank you so much! You are right, this is a start to clear up a major blur in my design. I had the ContextRules table sitting off by itself linked over to the course "entities" in a unnatural way. I'm going to change the names, but now it's laid out in a heirarchy like it should be...

Course->CourseActivities->CourseObjects{Lectures, Tests, Flash}

It did look like...

Course

     |
CourseObjects <- ContextRules

I still need to figure it out, but I see how I'll be able to derive attributes, like occurence, through inheritence much more efficiently this way.

It's pretty cool that I created a psuedo-IsA relationship on my own though, and didn't completely screw it up, haha. The stupid thing is I've used IsA and HasA relationships in C++ many times and it didn't even occur to me... sigh.

Again, thank you so much!

Eric

> 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 - 12:00:06 CEST