Re: Separate PK in Jxn Tbl?

"James A. Fortune" <MPAPoster_at_FortuneJames.com> wrote in message news:%23y9kCoIYIHA.5164_at_TK2MSFTNGP03.phx.gbl...

> Personally, I don't take the natural keys out either, so they can still
> be used for the deletion.

There are really two issues being discussed in a single discussion here.

The issue of synthetic keys versus natural keys is one issue. The issue of a composite PK in a junction table, made up of FKs, versus a new simple key is a separable issue.

Please note that, if the two FKs under discussion both reference synthetic PKs, all of your arguments concerning the problems of dealing with natural keys become moot.

If we have three tables, Students, Courses, and Enrollments, where enrollments is a junction between Students and Courses, we could have a synthetic key, StudentID for students, and a synthetic key, CourseID, for Courses.

The question then remains which is simpler. To define enrollments with a composite key
(StudentID, CourseID), or to define a new synthetic key, EnrollmentID. Neither of these two solutions uses natural keys.

I prefer to se natural keys whenever possible, but I use synthetic keys when natural ones just won't do. When do natural keys fail to do the job? When the poeple who control them are mismanaging them. Received on Sun Jan 27 2008 - 14:46:41 CET