Why, Blog, Why?

In the late 1990's I took several professional development courses at a WPI satellite school, which eventually led to my current career track as a web application developer. One of the courses involved an introduction to database theory. At the end of the course the professor passed out a page of useful SQL commands that we would probably have need of at some point in our lives. I've held on to it for a long time and have referenced it often. I've decided to post it here for two reasons: It's easier for me to find this way (and I can bookmark it), and someone else might find it useful. The tips were specific to Oracle, but I imagine (I'm not a DBA so don't take my word for it) that most of these commands, if not all of them, are valid under many modern Db engines.

Useful SQL Commands.pdf

A colleague of mine wrote to ask me for advice with updating a 50,000 record strong Access database. The database was originally built to use a customers Social Security Number as the primary key. This was smart thinking since a persons SSN is guaranteed to be unique (miskeys and identity theft excluded) and requires no other arbitrary identifier to describe or reference the patient record. Now they found themselves in the midst of a wave of recent patient privacy legislature and sought to get rid of the SSN field.

Her initial thought was to use a combination of the first four letters of the last name and the last four digits of the original SSN. Though the chances of finding a duplicate are slim (there are 10^⁴ possible combinations of the last-4 digits in a SSN alone, not including the 24^⁴ possible combinations of the first four letters of the last name), but there is still a chance of two records being created with “smit1234″ as their new key so this method is best avoided. (I have some other thoughts on arbitrary vs. rational, singular vs. compound keys which I’ll post later). I instead urged her to to go with the old fashioned auto-incremented numeric identity column and offered her the following steps for going about the conversion:

1. Make a backup copy of the database - just in case
2. In your main table, create a new column named ID or patientID or whatever is appropriate. I’ll assume it’s just ID for now. Create it as type AutoNumber. Under the General properties tab set New Values to “increment” and Indexed to “Yes (no duplicates)". Move the column so that it is the first one in the table, then save your changes.
3. Look at the data to make sure the new column is populated with an incrementing ID number.

If you have data in tables that are linked to the main table, then for each linked table

1. Create a new column named patientID or whatever is appropriate. Set is as type Number. Set the Indexed property to “Yes (duplicates OK)". Save your changes.

2. Create a new Query (just accept all of the default options) and switch to the SQL view. We are going to write a query that will update the new patientID column with the new AutoNumber that we created above. The query will be different depending on the name of the linked table, but it should be similar to this:

   UPDATE [linked_table_name], [main_table_name] SET [linked_table_name].[patientID] = [main_table_name].[ID] WHERE [main_table_name].[old_social_security_column_name_from_main_table] = [linked_table_name].[old_social_security_column_name_from_linked_table]

3. Verify that the data was updated.

Finally

1. Remove the old SS# column from the main table
2. Set the new ID column as the Primary Key
3. Remove the old SS# from any linked tables
4. Update your old queries, forms or reports to reflect the changes.

From dublish.com