I think that creating a new primary key is the only solution to this problem, but I would love to have other input, including potential pitfalls I might encounter in this process.
Here is the scenario: I have spent the past year creating a "survey based" database in my "spare" time at work. The database holds detailed information about nutrition questionnaires given to children over the years that they are in our program. I have based the structure on using ClientID as the primary key. ClientID = each individual's social security number.
We have another database that holds all the client's information. This is the database where any updates are done to their information. I export the client information (firstname, lastname, birthdate, socialsecurity, plus a few more) that I need into excel. Then I import that info into my database on a regular basis using an update/append query.
We started using the database in September and all was going well until a few weeks ago when I imported data and found a name that was entered twice. On looking closer, I found that the person had two different ClientID's. How could this happen...? Well, in our other database, if a child doesn't have a social security number yet, or we weren't given it, the program creates a fake social security number AAA-##-####. I knew this from the beginning, but wasn't aware of any problem. However, I was missing a significant piece of information. When the child finally does receive a SS#, we go in and change the number in the program!!!! At first I was told this rarely happens (I had about 6 duplicates the first time), but today I ran another import and found 90 instances of duplicated names!
There are just over 600 records in the database. Many of these have had information that is unique to this database entered in a related table, so I need to be sure I don't lose any of that data. There is data in other tables that I could import again if I have to.
Here are my options as I see them:
1. Leave the primary key alone and delete the duplicated values with every update.
2. Create a new unique autonumber primary key, knowing I will have to delete relationships and rebuild them. I would leave the clientID field in the system as it is the only way we have to determine the difference between two children with the same names and birthdates.
3. Run quietly away hoping they don't notice the problem until I am long gone.
What would you do in these circumstances? If you agree with my assessment that I really should create a new primary key, what steps would you take to minimize problems both during the process and in the future?
And yes, I wish about 12 months ago, I had asked if the social security number was ever updated, but of course no one thought to bring that up when we were discussing the database design!
Thanks for your help!
Here is the scenario: I have spent the past year creating a "survey based" database in my "spare" time at work. The database holds detailed information about nutrition questionnaires given to children over the years that they are in our program. I have based the structure on using ClientID as the primary key. ClientID = each individual's social security number.
We have another database that holds all the client's information. This is the database where any updates are done to their information. I export the client information (firstname, lastname, birthdate, socialsecurity, plus a few more) that I need into excel. Then I import that info into my database on a regular basis using an update/append query.
We started using the database in September and all was going well until a few weeks ago when I imported data and found a name that was entered twice. On looking closer, I found that the person had two different ClientID's. How could this happen...? Well, in our other database, if a child doesn't have a social security number yet, or we weren't given it, the program creates a fake social security number AAA-##-####. I knew this from the beginning, but wasn't aware of any problem. However, I was missing a significant piece of information. When the child finally does receive a SS#, we go in and change the number in the program!!!! At first I was told this rarely happens (I had about 6 duplicates the first time), but today I ran another import and found 90 instances of duplicated names!
There are just over 600 records in the database. Many of these have had information that is unique to this database entered in a related table, so I need to be sure I don't lose any of that data. There is data in other tables that I could import again if I have to.
Here are my options as I see them:
1. Leave the primary key alone and delete the duplicated values with every update.
2. Create a new unique autonumber primary key, knowing I will have to delete relationships and rebuild them. I would leave the clientID field in the system as it is the only way we have to determine the difference between two children with the same names and birthdates.
3. Run quietly away hoping they don't notice the problem until I am long gone.
What would you do in these circumstances? If you agree with my assessment that I really should create a new primary key, what steps would you take to minimize problems both during the process and in the future?
And yes, I wish about 12 months ago, I had asked if the social security number was ever updated, but of course no one thought to bring that up when we were discussing the database design!
Thanks for your help!
