Key Violations

[Daryl2106]

Hi,

I am trying to update returning student enrollment records to reflect 2012 course#'s (a student can return to the same course in a subsequent year, but under a new course #). I tried to submit a screen shot of the UD query but it was too large so I zipped it (attached). I am getting a key violation on 230 of the 330 records that need to be updated. In the ContactInformation table the primary key is the StudentNumber field. I noticed that some of the 2011 ClassNumbers are the same as the 2012 ClassNumbers. (I am sent these from our registrar and cannot change them). Is this what is causing me the problem?

Thanks and take care,

Daryl

[June7]

If you want to update with 2012 numbers, why is the link on ClassNbr2011?

ID field in CourseInventory is set as primary key but this is not the value being saved as foreign key in StudentEnrollment?

Are you going to keep adding ClassNrYYYY fields to CourseInventory every year? This is not properly normalized data structure.

[Daryl2106]

Thanks June,

That makes perfect sense. I probably set this relationship up early in the creation of the DB when I knew even less than I know now . If I change the foreign key to the IDfield will it create problems for he current tables? And yes, it seems that classnumbers will change annually.

Take care,

Daryl

[June7]

Yes, changing the pk/fk links will cause problems. Must be done carefully. Need to create a number field in StudentEnrollment then populate this field with the ID value from CourseInventory. Then change links. Might have to modify queries before modifying link in Relationship builder.

Having to add a new field each year for class numbers is poor design. How will you know which year(s) the student attended the class? Normalization would require a new field called ClassYear. This could also mean another table of class descriptions. Normalization will also require a 'junction' table of records that will associate student with the class numbers. So for each year that a student takes the same class, there will be another record in this junction table. Consider:

tblCourses
CourseID (PK)
CourseName (or this could be PK)
etc

tblClasses
ClassNumber (PK)
ClassYear
CourseID (FK)
etc

tblStudentClasses
StudentID (FK)
ClassNumber (FK)

[Daryl2106]

Thanks June7,

I find your replies most helpful for a beginner like me. I only get a little time to work on this DB but the changes you suggested are working well so far and easy to follow. I'll let you know when they are complete. I can see how this will make the DB much more functional, reliable and update friendly. I'm Learning!

Take care,

Daryl

[June7]

Getting the data structure correct is critical first step in development. You seem to be on track now. But if you want to review a basic primer, look at http://forums.aspfree.com/microsoft-...es-208217.html