Data Issues: changing ids

After discussing my woes about ODS (where our class/student/instructor data comes from), a developer pointed out I should be noting all this stuff down. It's a good reference for explaining why our project took some of the turns that it did, and it may save another developer some headaches. So, here's the first of these.

When we first started designing eCAFE, we were told that the person_uid and id_number fields were the primary values used to identify a person in the database. I don't recall if someone told us this explicitly, or we just assumed it, but it was our belief that the numbers were unchanging. This turned out not to be the case.

While either number was fairly constant, during our updates each semester, we would inevitably find a few that changed. Since we built our tables using person_uid as the foreign key that all other tables referenced, this caused big problems. I finally had to write a script to do the following:

1.) Remove the unique index from the person table on the id_number field.
2.) Add a new record with the new person_uid (with same id_number, hence #1).
3.) Go to all dependent tables and change the person_uid to the new value.
4.) Remove the original record in the person table.
5.) Restore the unique index.

What a pain. In the redesigned eCAFE we autogenerate a unique key for each person and use that as the FK in the dependent tables. Now we can change the person_uid field with a simple update statement.