Relational database domain integrity
Hello,
In episode 3 of the domain integrity section of the relational database design course, an ID field was added to the ClassesTrainers Table. Since this is now the primary key for this table, wouldn't this allow for the same trainer and class combination appearing more than once in this table?
Thank You,
Daniel King
Great question!
You are right, because the ID field in the ClassesTrainers table is the primary key, this would allow for the same trainerID and classID combination to appear more than once.
At first glance, you might be tempted to create a compound primary key consisting of the TrainerID and the ClassID to prevent this. This would indeed prevent any combination of TrainerID and ClassID from being duplicated. This would work fine, until the trainer had to teach the same class again, next month. It is very likely that a trainer would teach the same class several times.
So really, it is the scheduled instance of the class that is unique. James teaches Puppy Kindergarten several times. But James teaches Puppy Kindergarten on Feb 2nd, 2018 only once.
By using an ID field in the ClassesTrainers table, we can allow for a trainer to teach the same class several times. The combination of the class, the trainer and the scheduled date is still unique.
Hope this helps, let me know if you have more questions!
I thought that the ClassesTrainers table was just for listing which trainers were qualified to teach what classes. The issue of the same trainer teaching the same class multiple times would be handed by the scheduled classes table.
Thank You,
Daniel King
I see your point now, and you are right. The addition of the ID field in the ClassesTrainers table will allow for duplicates, which we do not want.
After watching a few other episodes, I found out what happened. Initially, the ClassesTrainers table had a composite primary key (PK) made up of the ClassID and the TrainerID. This ensured there were no duplicate entries in this table.
Then, in the Domain Integrity Part 3 episode, we found a problem when inserting new records in the SchClasses table. We could schedule any class with any trainer, regardless of the information in the ClassesTrainers table. There was a flaw in our design. To fix this, we changed the design of the ClassesTrainers table to include an ID field, and we set that to be the PK, and added the ID field as a foreign key to the SchClasses table.
This fixed the problem of scheduling classes, we could only select combinations of class/trainer that existed in the ClassesTrainers table. What we failed to realize, is that we could now duplicate information in the ClassesTrainers table.
Great catch on your part!
To solve this and make sure we cannot have duplicate data, we need to create a unique index that covers the combination of ClassID and TrainerID fields. This is what a primary key does, but we can only have one PK. A unique index will allow us to enforce our unique requirement, while still using the ID field as the PK.
To view existing indexes and create a new one, open the table in design view. In the design ribbon, select Indexes
Click in the first blank row under Index Name, and type a name for the new index, something like ClassTrainer. Then use the dropdown list in the Field Name column to select the ClassID field.
To create a composite index, we simply add another field to the same index name. In the next row, leave the index name blank, and use the Field Name dropdown list to select the TrainerID field.
Almost done. Now select the index name and you will see the properties of the new index. Notice Unique is listed as "No"
If you click where it says No, you will see it is a dropdown list. Change it to Yes.
That's it. Close the Indexes window and save the changes to the table. Switch to datasheet view and try to insert a duplicate record.
Now we can use the ID field as the primary key, and still ensure that the combination of Class and Trainer is unique!
