Re: Newbie question about db normalization theory: redundant keys OK?

On Dec 14, 11:49 am, "David Cressey" <cresse..._at_verizon.net> wrote:

> An example of a compound key. An enrollment system for courses, students,
> and enrollments.
> An enrollment might be called a student-course. In the MS design pattern,
> they will create keys for each table:
>
> StudentId for Students,
> CourseID for Courses,
> and StudentCourseID for StudentCourses.
>
> Lets accept for now that StudentID and CourseID are good ways to identify
> students and courses. Other participants will object, if the past is any
> guide, but you can analyze their objections for yourself.
>
> I claim, at this point, that StudentCourseID as a primary key for the third
> table, the StdentCourses table, is a bad idea. A better idea is to let
> StudentID and CourseID be a compound primary key for StudentCourses.
>
> These two columns will be in the database you get from the wizard anyway.
> They have to be there to serve as foreign keys, relating the row in
> StudentCourses to parent rows in Students and Courses. There is little to
> be lost, and much to be gained, by making these two column the compound
> primary key of this table.

OK, I just did this, and I would greatly appreciate your feedback at some point.

I have three tables, STUDENT table, with StudentID (PK), COURSE table, with CourseID (PK), and a third table STUDENTCOURSE, which within Design View I was actually able (without using the Wizard!) to set up a compound key using this trick: make sure the two fields (shows an rows but they're fields of course) you want to be the compound primary key are next to each other (this is important it seems), then holding down the "Control" button, select both 'rows' (I used the mouse, but arrow keys probably also work), then right click and select "Primary Key" (symbol), then the little "PK" key will show up next to both fields! Pretty cool, now both fields are your primary compound key. Then, in the relationship window, I enforced Referential Integrity (always a good thing from what I can tell) for this compound key of table STUDENTCOURSE with the PKs of both STUDENT and COURSE. It's a non-exclusive, one to many relationship so I had the "1" symbol running from both the first two tables to TWO "infinity" symbols (for many) running into the third table. Seems to work fine.

If anything doesn't look kosher please let me know.

The first time I did this I didn't have a true compound key, since for reasons too convoluted to mention now, but I will anyway, my second table (COURSE) had a foreign key that was in fact the migrated primary key of the STUDENT table, so essentially I was using as a compound primary key in STUDENTCOURSE the primary key of STUDENT alone (why this didn't end up as a compile error I don't know, since it's illegal, perhaps I played with the properties checkboxes in such a way that I allowed duplicates to be formed, but then again I didn't really test it, and perhaps it would have failed had I run a query).

I think I'm getting the hang of this RDBMS stuff, it's pretty neat.

David Cressey, thanks!

RL

PS--at some point I'm going to have to find out why a compound key is preferred sometimes, and I'm not talking about the semantics of what a compound key is, but that's further down the road and this is neither the time nor place for that probably. Received on Fri Dec 14 2007 - 22:43:35 CET