Social Security and Id numbers

[Vivian]

I designed a database several years ago for an
organization which used social security numbers for its
identification numbers. Well now they want to change to
a 10 digit number with leading zeros. I thought this
would be an easy fix but it is not turning out that way.
The original Id field is text with ss input format. The
new Id field needs to be a number with 0000000000
format. The user has not yet let me know if the ss
numbers are to be deleted altogether. Initially I had
thought I could change the name of the old Id field then
create a new Id field with the name of the old field and
it would feed into the db's many forms. It is not
working as well as I had hoped. I have to change the
format of the field on each form and some macros which
look up prior history for the ids are just not working.
If someone has done this type of conversion< I'd
appreciate your insight. TIA
Vivian

 

[Tim Ferguson]

I designed a database several years ago for an
organization which used social security numbers for its
identification numbers. Well now they want to change to
a 10 digit number with leading zeros.

Very sensible -- although I am on the "wrong" side of the atlantic,
everything I hear says that SSNs are bad ideas for identifiers.

Is there any reason not to use a straightforward Autonumber for an
artificial key? You'd have to remember to set the format for it on forms
and reporst to "0000000000" but that is not much of a price.

Although I don't know very much about your app, it seems that it would be
worthwhile keeping the old SSN field as well as the new PersonID number.
Unless, of course, there is some legal reason that it has to be expunged.

Remember too, that changing a major identifier is a pretty fundamental
design upheaval. Anything you don't have to go back to the drawing board
on, you should count yourself lucky with! You'll probably need a host of
update queries to get the foreign keys pointing to the new values, and then
a whole bunch of testing.

Good luck and Best wishes


Tim F