Re: Compositkey dilema...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Tom Wickerath wrote:

Vincent,

Problem 1, that the SQL to delete a record is simpler with one key, is correct, but both of the examples shown there work properly, so if you don't delete records often, it may not matter to you.

..."so if you don't delete records often, it may not matter to you"? I'm not sure what that statement is suppose to mean. Do you really want to create solutions where deleting records is a bit more difficult than it needs to be?

Yes, indeed. Because it's not MUCH more difficult, and the table structure is simpler. It's a trade-off: two fields vs. three, but a slightly longer SQL statement (which you may never look at again). How much do you care that the computer needs to do slightly more work, if you can save space? (The answer depends to some extent on what resources you have available.)

What about the example of allowing a student to take a class multiple times, or any other situation, where you might need multiple entries in the junction table from tbl1 and tbl2? As the author pointed out, this would require one (or more) additional fields be used as the combined PK.

I would want to determine *ahead of time* if multiple records with the same key should ever be allowed. If I know that the second such occurrence will supersede the first one, or if there is some external reason that duplicates are never proper, there should be no problem. Otherwise, I shouldn't do it this way.


Problem 2, that the key might not be unique, might not apply to you. You'll probably want to define a "No Duplicates" index on your junction table...


Well, since the title of the article is "Why to avoid composite Primary Keys", you would have already had a "No Duplicates" multi-field index on the foreign key fields that made up the combined PK for this table. I agree that you may want to define a "No Duplicates" multi-field index on these two foreign key fields if they are not used as a primary key, but by doing so, you re-introduce the problem discussed in paragraph 1 (ie. allowing a student to repeat a class multiple times). I can't really think of too many situations where the bound column of a combo box is not unique in the databases that I create.

I agree, but how is a "No Duplicates" index going to allow something that a combined PK won't? You can't enter duplicate sets of values in either case.

Problem 3 also seems based on a premise that the combined key might not be unique. If I understand this correctly, see my suggestion concerning Problem 2.

I don't think you understood it correctly. The author was saying that if you need a new table that is related one-to-many (1:M) to your junction table (tblStudentClasses in this example), then you will need to include three pieces of information in your new table (tblStudentClassTests) : StudentID, ClassID and TestID. This would require two relationship joins defined to enforce referential integrity:

tblStudentClasses.StudentID ----> tblStudentClassTests.StudentID   and
tblStudentClasses.ClassID ----> tblStudentClassTests.ClassID


Tom

http://www.access.qbuilt.com/html/expert_contributors.html

OK, I think I see what you're saying -- that if (in Problem 3) you have to set up a separate table, [tblTests] for multiple tests for each student for each class, you burden this new table with an 8-byte foreign key instead of a 4-byte key. But in this case, couldn't you dispense entirely with [tblStudentClasses]? In [tblTests], you could either let the 2 fields from [tblStudentClasses] plus the new [tblTestsID] field form the (combined) Primary Key, or just specify that [tblTestsID] be an Autonumber or otherwise unique without reference to any other fields.

So your choice would be either to

(1) include both [StudentID] and [ClassID] as foreign keys in [tblTests], and erase [tblStudentClasses], or

(2) include just one [tblStudentClassesID] key to link to [tblStudentClasses].

I suppose there might be valid reasons to choose (2), such as if somebody else maintains it and you have to get permission to make changes, but every time you use it your Query has to do 2 indirections. I'm not opposed to making a computer do the work that a human being might otherwise have to do, but this seems kind of unnecessary.

Option (1) is about the same as changing the design of [tblStudentClasses] to add fields to it of [tblTestsID] (unique) and [TestData], and resetting its primary key to be the new [tblTestsID].

I claim that both options will be correct, but that (1) is preferable because it will probably require less space and fewer memory references.


"Vincent Johns" wrote:

The article makes some valid points, but consider them in light of your own situation.

Problem 1, that the SQL to delete a record is simpler with one key, is correct, but both of the examples shown there work properly, so if you don't delete records often, it may not matter to you.

Problem 2, that the key might not be unique, might not apply to you. You'll probably want to define a "No Duplicates" index on your junction table, and your Combo Box would thus select unique values. You'll need to be sure that the values really ARE unique, perhaps by trying to add duplicate values and being sure that you get an error message. (Or maybe I don't fully understand the writer's objection -- take what I say with a grain of salt!)

Problem 3 also seems based on a premise that the combined key might not be unique. If I understand this correctly, see my suggestion concerning Problem 2.

In your case, if your junctional table does *not* need to have unique keys, then you'll need some way to distinguish those that share a set of keys, and by the time you add enough extra keys to make the records unique, you might save a lot of trouble just by adding an Autonumber key to the table and using that as your primary key. 
[...]

What I meant was that by the time you have some key in your table that is guaranteed to be unique, you can make that be the table's primary key.

Incidentally, I normally avoid using multiple-column combo boxes. Instead, I define a Query with only two fields: a key, and a computed field that is easy for the user to understand (such as first name, space, last name, sorted by last name) and in the Combo Box or Text Box display only the 2nd field but use the 1st field as the chosen value. This is lots easier than trying to re-design existing fields to make them easier to read.

  -- Vincent Johns <vjohns@xxxxxxxxxxxxxxxxxx>
  Please feel free to quote anything I say here.
.

Relevant Pages

  • Re: Combo Box Design and/or Coding
    ... Primary Key: HotelID, Indexed, Yes (No Duplicates), Data Type: AutoNumber ... Primary Key: LocationID, Indexed, Yes, Data Type: AutoNumber ... nothing is being updated in tblHotelLocations. ...
    (microsoft.public.access.formscoding)
  • Importing data from different databases.
    ... If you change the primary key then for the best results ... it's possible that we could have duplicates ... >How can we resolve this problem? ...
    (microsoft.public.sqlserver.programming)
  • Re: Deleting Duplicates in the same table.
    ... If you can't pick one record of the duplicates out from another, then I know of no good method to delete all but one of the duplicated records other than the methods outlined below. ... Build a query based on the table that will identify the primary key values you want to keep and save that as qKeepThese. ...
    (microsoft.public.access.gettingstarted)
  • Re: Deleting Duplicate Records based on "Text" column
    ... CONSTRAINT PRIMARY KEY NONCLUSTERED ... This query in QA produces 411 rows that are duplicates, ...
    (microsoft.public.sqlserver.programming)