Re: Newbie question about db normalization theory: redundant keys OK?
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 15 Dec 2007 10:07:09 -0400
Message-ID: <4763df91$0$5290$9a566e8b_at_news.aliant.net>
>
> anyway.
>
>
> to
>
>
>
> 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.
>
>
>
> 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.
>
>
>
> 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.
>
> 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!
>
>
>
> You're welcome!
>
>
> I hinted at the answer above.
Date: Sat, 15 Dec 2007 10:07:09 -0400
Message-ID: <4763df91$0$5290$9a566e8b_at_news.aliant.net>
David Cressey wrote:
> "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.