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

"raylopez99" <raylopez99_at_yahoo.com> wrote in message news:692ee78d-c398-4cf2-b56f-7cd41db204da_at_d4g2000prg.googlegroups.com...
> 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.

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.

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!
> 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.

I hinted at the answer above. Received on Sat Dec 15 2007 - 13:56:40 CET