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

From: Bob Badour <>
Date: Sat, 15 Dec 2007 10:07:09 -0400
Message-ID: <4763df91$0$5290$>

David Cressey wrote:

> "raylopez99" <> wrote in message

>>On Dec 14, 11:49 am, "David Cressey" <> 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.

> Good for you!
> There is a way to declare a compound key without using the trick you
> mentioned.
> It involves something like holding the CTRL key down while you select each
> of the columns.
> The danged GUI is so intuitive that only a child can do it!
> Behind the scenes, MS access made an index when you declared the PK. That
> index could make joins run faster. I really don't know as much about the
> Jet engine as I do about the Oracle engine.
> One thing you gained at the logical level is this: if you try to enroll the
> same student in the same course more than once, Access is going to gripe at
> you when you try the second insert. Presumably, this is what you want. If
> you had used GUIDs on the STUDENTCOURSES table
> as you have previously preferred you would have had to program in this
> constraint. By choosing the above PK, you got it automatically.
>>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.

> In general referential integrity helps you and doesn't hurt you. If you
> were to try to enroll a student in a course that doesn't exist, without
> referential integrity, you wouldn't get an error. With referential
> integrity, you get an error. You may end up using some tool like "Combo
> Boxes" to achieve the same result without checking referential integrity in
> the relationships diagram, but it can't hurt to check the box.
>>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 don't know how you ended up with a StudentId in the COURSE table, but I
> hope you corrected that in your second try.
> I have to admit that I was slow at realizing the importance of the
> Relationships window in MS Access. I didn't declare these relationships in
> my first attempt with MS Access. Then when I went to create queries that
> used more than one table, I was up the creek! The only way I could get
> around it was by switching from Design view to SQL view and coding in the
> join conditions myself. But using the relationhips window is the easy way
> to get the right join conditions. You don't have to repeat my errors,
> especially since you are learning Access before you learn SQL.
> If you set up the relationships correctly in the relationships window,
> then, when you compose queries using the design window, Access will
> already "know" how the tables are connected to each other. I'm tempted to
> say "how the tables are linked to each other", but the Grand Inquisitors in
> this newsgroup will be quick to brand me an ignoramus if I use terms they
> have already put on their index of prohibited terms.
> If you read MS Access documentation, you'll note that they use terms like
> "Record" and "Field" instead of terms like "Row" and "Column". That's
> enough to get you tried for heresy in here. It's one of the few things that
> Bob Badour and Joe Celko agree on.

I suggest you let Bob speak to what he agrees on. Jet is a file processor and MS Access is an end-user reporting tool. They can call things whatever the hell they want. None of it is relevant to data management.

> Speaking for myself only, it's lees important whether you use the correct
> terms than whether you undertand the underlying concepts. I think your
> learning is progressing in the right direction.

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

> It gets even neater!
> When you have a database that consists of about 100 tables and 900 columns,
> and you have 50 concurrent users trying to read and update data all at once,
> and
> the database is on the air 364*24, with one day a year for reorgs, and you
> need off site backups in case of a fire, and the government is breathing
> down your neck, and the requirements keep changing, you'll need all the
> good sound design under you that you can get!
>>David Cressey, thanks!

> You're welcome!
>>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.

> I hinted at the answer above.

Ray, the design criteria for keys are: familiarity, simplicity, stability, uniqueness and irreducibility. Sometimes the criteria conflict, and design involves tradeoffs.

If one has a very simple predicate with a handful of attributes that are all foreign keys to other relations and with no references to itself, then familiarity, stability, uniqueness and irreducibility will trump simplicity.

Other situations are less clear-cut. Received on Sat Dec 15 2007 - 15:07:09 CET

Original text of this message