UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Closed TopicStart new topic
> Why to avoid composite Primary Keys    
 
   
JeffK
post Jul 21 2003, 11:05 AM
Post#1


UtterAccess VIP
Posts: 1,550
Joined: 19-December 02
From: Lansing, MI


I posted this recently concerning why composite primary keys are bad, and have had a few requests to put it here. Here is the situation:
You have this setup:
Table tbl1 with ID1 as the Primary Key
Table tbl2 with ID2 as the Primary Key
Table tblJunction that contains valid combinations of records from tbl1 and tbl2
A very tempting, but incorrect, Primary Key for tblJunction is a composite one consisting of the two foreign key fields ID1 and ID2. However, the Primary Key, as always, should be a single-field, preferably AutoNumber. Here is why having a composite key is not a good idiea, however tempting:
To be more concrete, let's say you have people from tblStudents that have taken classes from tblClasses and you identify who has taken what class in tblStudentClasses. We start out assuming each student can take the class only once, so tblStudentClasses should have unique combos of StudentID and ClassID.
Problem 1
You have a form where you select a student and his classes show up in a listbox. You want to give the user a way to remove the student from a class in case there was a mistake.
THere is the code if you have a multi-field primary key:
Currentdb.Execute "DELETE FROM tblSudentClasses WHERE StudentID = " & me.cboStudent & " AND ClassID = " & me.lstClasses.
Here is the code if you have a single-field primary key:
Currentdb.Execute "DELETE FROM tblStudentClasses WHERE StudentClassID = " & me.lstClasses
Which is better? When you want to single out a record for action, it is faster and less code to have that record flagged with a single unique value to tell the database which one you're talking about. Telling the database which record you want to delete has nothing to do with what uniquely identifies the record to you. In the first method, your code needs to know something about the information in the record, not just its number, and if you ever decide to allow a student to take a class multiple times you have to change this code to include your new field of the PK (like class date). The point is, a single-field PK doesn't care what is in the record, it just flags it with a meaningless value so all VBA code, relationships and queries can refer to it without knowing anything about it.
Problem 2
What if you wanted to select a record with a multi-field PK from a combo box?
John Calculus
John Chemistry
Jane French
Jane Biology
If the bound column is not unique, you can select the second record "Chemistry" and watch the combo box display "Calculus" for its column 2 value. It does this because as far as it is concerned you picked "John", regardless of which one of John's classes you picked, so it just shows the first "John". Try it. It's annoying! Again, the combo box doesn't care that the combination of John and Calculus is meaningful to you, it just wants to know what record number you're talking about.
Problem 3
Now you have tblTests with TestID and want to create tblStudentClassTests to show what each student got on each test for each class. If tblStudentClasses does not have a unique key, you now need three pieces of information in your new table : StudentID, ClassID and TestID. If you have a single field PK, you only need StudentClassID and TestID - the actual student and class can be determined from StudentClassID. It is in this next layer you see that adding the single-field PK in the long run actually saves you space for larger models...
Every time I have seen a case for using a multi-field Primary Key it has always come from a concept that the Primary Key should mean something, or that it should describe the rest of the record. This is not the case. Notice that in every description of database normalization, it says the other fields describe the Primary Key, not that the Primary Key describes the other fields. It should only be used to uniquely flag the physical record in the database with no understanding or concern of what is in that record. In that regard no table is ever any different from any other table when it comes to adding a Primary Key.
If you need to identify the record for your own purposes or for data integrity, you can add a compound index or a SSN or whataver you like but using that as the record's identifier for VB, relationships, etc. will only cause extra code and possible future maintenance.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 02:59 PM