Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!newsfeed1.earthlink.net!newsfeed.earthlink.net!stamper.news.pas.earthlink.net!newsread1.prod.itd.earthlink.net.POSTED!not-for-mail From: "Eric Gibson" Newsgroups: comp.databases.theory References: Subject: Re: Foriegn key which could point to multiple tables? Lines: 158 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-Mimeole: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: Date: Mon, 05 May 2003 10:00:06 GMT NNTP-Posting-Host: 209.86.129.241 X-Complaints-To: abuse@earthlink.net X-Trace: newsread1.prod.itd.earthlink.net 1052128806 209.86.129.241 (Mon, 05 May 2003 03:00:06 PDT) NNTP-Posting-Date: Mon, 05 May 2003 03:00:06 PDT Organization: EarthLink Inc. -- http://www.EarthLink.net Xref: core-easynews comp.databases.theory:26168 X-Received-Date: Mon, 05 May 2003 03:01:37 MST (news.easynews.com) "Paul Keister" wrote in message news:Dngta.207$H37.31688070@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" wrote in message > news:ewfta.54174$ey1.4980500@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 > > > > > > >