I still have to ask, WHY did the ID field change for this record, and
how likely is it to happen again? For a field named "ID" or "PatientID",
the function of the field is clearly to BE a primary key, not just
look like one.

1. This database does not assign the IDs, and an ID of a new patient
was entered incorrectly. Here you have a data problem, and manually
fixing the affected record(s), mostly with UPDATE, is appropriate.
If done often enough, you may need a procedure or application program
to do this, while tracking down and changing the references in other
tables. You do not want to dink around with adding fields.

You may be able to get this down to a fairly simple script
that fixes all the PatientID fields in all tables:

START TRANSACTION;
.... duplicate 'patient' record with PatientID = <old id> giving it
a new PatientID of <new id>;
UPDATE appontments SET PatientID = <new id> WHERE PatientID = <old id>;
UPDATE visits SET PatientID = <new id> WHERE PatientID = <old id>;
UPDATE test_results SET PatientID = <new id> WHERE PatientID = <old id>;
UPDATE bill_items SET PatientID = <new id> WHERE PatientID = <old id>;
UPDATE billing SET PatientID = <new id> WHERE PatientID = <old id>;
....
UPDATE invoices SET PatientID = <new id> WHERE PatientID = <old id>;
DELETE FROM patients WHERE PatientID = <old id>;
COMMIT;

This should work if there are no unique indexes on any field other
than PatientID in the table patients, and if no referential integrity
constraints on fields other than PatientID get in the way.

If referential integrity is not enforced, you can simply update the
PatientID in the record in the 'patient' table, without duplicating
the record and then deleting the old one, and fix all the tables
that reference it as above.

2. This database does assign the IDs, and due to some kind of
error, it assigned a duplicate ID, or a small number of them. (Say,
someone typed ALTER TABLE patients AUTO_INCREMENT = <typographical
error> by mistake, then fixed it to the correct number, but a few
patients got created in between the two. Or a disk sector got
smashed.) Here you have a data problem, and manually fixing the
affected records, mostly with UPDATE, is appropriate.

Presumably the incorrect IDs assigned are not duplicates of live
records in the tables, as creating these would get duplicate key
errors, but they might be duplicates of old, archived data. I am
here imagining a setup where records of patients inactive for more
than N years are archived in another table or offline (perhaps for
efficiency purposes), but data for inactive patients who schedule
appointments is brought back during an overnight maintenance
procedure.

3. Two practices are being merged, and the patient IDs of existing
records overlap. Here you really want to change the primary key,
possibly of ALL the records, in a hopefully one-time change. You
might try an approach of adding 1000000 patient IDs for one set of
records (assuming both were generated with auto_increment and haven't
gotten over 1000000 IDs yet), add 2000000 to the other set, and
merge the records. Then set the new auto_increment value to 3000000.
You might want to keep old ID and old Practice columns around to
match with old paper records and ID cards. You add to BOTH sets
of IDs so unconverted IDs are recognizably different from converted
ones when they are encountered in paper records, ID cards in the
hands of patients, or something you forgot to convert in the database.

Records where the same patient appears in BOTH practices will
probably have to be merged manually, as these will appear as 2
different patients after the merge. Identifying this situation
alone may be a problem, as you may not have enough information in
the records to tell whether two records, one out of date by several
years, refer to the same person.

(I wonder if anyone can suggest how to future-proof generation of
new bank account numbers, where it seems that banks in the USA
usually merge during their lifetime, perhaps several times, but
there's no way to predict which bank they will merge with ahead of
time. A solution I've seen used in this situation is adding prefixes
to fixed-length account numbers to make longer fixed-length account
numbers).

4. The patient objected strongly to their ID number on religious
grounds, and insisted that it contain 666. The fix is the same as #1.

Auto-incremented keys are particularly vulnerable to business mergers,
as when you try to merge databases, most of the key ranges will overlap,
even assuming the same software and database schema were in use.

No I didn't... read again, but I hear your advice.

The ID changed due to government policy... Here in Canada we have
government involved in hospitals.
Some times it's provincial, municipal or federal. So in a country
where you can go from state to state your health record can follow
you.
In my case yes. The patients ID can change over time. It's a mess
tracking down all the references to that ID, but that's the way it is.

I think sheldonlg was right.. there should be a record number that is
unique to the database...

SpreadTooThin:
Excuse me for asking again. Perhaps my old brains simply don't switch
as fast as they used to.

A patient (A) in one Canadian provence has ID 12345.
Now, Patient-A moves to another provence, and registers at a hospital.
Is it really true that he gets registered with ID 12345 first, and then
his ID gets changed to a different ID?
Suppose someone else (B), in that second Provence already has ID 12345.
What's the rule then?

What type of government policy can make the database PK change?


--
Erick

SpreadTooThin wrote:
We have that down here as well. It is called the social security
number. However, that is national.

According to the Social Security Administration, the social security
number was not designed to be unique, and it isn't. Even if it
were, there are lots of identity thieves, and people who really
think their personally assigned SSN was given to them in a wallet
they bought. Also, not everyone in the USA has one or is eligible
to have one. (People in the USA as tourists can get sick too.)

As far as I know, the SSN doesn't change much except perhaps for
people put in the Witness Protection Program, and they shouldn't
be telling you that.

Gordon Burditt wrote:
That was before the days of "1984". I believe that they are unique.
There are, after all, 10 billion combinations.

Identity thieves can do that with any id that is chosen. Why, in
particular, is this counter to what I said?

You can pretty much tell the general area that a person lived in when he
got the number. The first three digits are more or less geographical.
Also, people with green cards get numbers like 999-xxx-xxxx (I believe).
That allows for 10 million of them. Tourists are another matter.
Correct. In fact, I believe it is illegal to have more than one.

No, that's a math error. xxx-xx-xxxx. 1 billion = 10**9.

That might mean that all SSNs assigned after 1984 are different
from all other SSNs assigned after 1984, but it doesn't mean that
older people with non-unique SSNs aren't still alive.

If it wasn't *designed* to be unique, you can have all the possible
combinations you want, and you still may end up with duplicates.
(Consider assignment of SSNs by rolling 10-sided dice for the
low-order 4 digits. I doubt they ever did that, but clerical errors
happen.) People actually have discovered that someone else has the
same SSN as they do, and on investigation, the reason could not be
tracked down to identity theft, SSN-in-a-wallet, or other popular
mistakes on the part of anyone but the government (and the government
does not admit that assigning the same SSN to two different people
*is* a mistake).

There are not 10 billion combinations of valid social security
numbers. There are closer to 988 million, due to 000-AB-CDEF,
666-AB-CDEF, ABC-00-DEFG, and ABC-DE-0000 not being valid. There
may be other combinations that will never be issued. About 442
million SSNs have been issued. Almost half of the combinations.

If you are an employer or especially credit bureau, you may run
into instances where your database will break if you try to use the
SSN as a primary key. And you may not be able to determine which
one is the identity thief, while you still have to issue paychecks,
and you aren't the identity thief police.

Besides that, the government has issued duplicates. I'd expect a
lawsuit if someone were fired due to having a duplicate SSN of
someone already employed by the company. I'd also expect that
anyone in IT saying they can't handle a new employee because of a
duplicate SSN would be fired if he didn't fix it fast.

Oh, yes, I should mention that in maternity wards there are a lot
of patients who don't have a SSN *yet*. Although mothers may apply
for a SSN for their newborn in the hospital, they don't usually get
one while they are still there. SSA says 2-11 weeks, by state, with
Illinois taking up to 11 weeks.

I don't believe that. 999-xx-xxxx I'll believe, which is 1 million.

No, it's not. There are a few reasons why you could get a new
number (applying for a different number, not just applying for a
number and not mentioning that you already have one). One of them
is "More than one person has been assigned, or is using, the same
number." (Yes, the SSA has assigned duplicates. Most have probably
not been discovered by the people involved.) Another is objection
to the digits used. Another is that sequential numbers for members
of the same family are causing problems. The old number is not
cancelled. These changes are still pretty rare.

Gordon Burditt wrote:
Correct. I had a long day.

The "1984" was a reference to the book and to "big brother", and to the
"intentions to not be a national identity number", which was explicit in
the legislation. The system was introduced in the 1930's.

If we take a population of 350 million people, all having numbers, that
leaves only a factor or three in numbers. IOW, there are two numbers
available from people who died for every one person still alive -- if we
were to reuse the number. If we assume based upon life expectancy that
currently about 5 million die each year, and it started 75 years ago,
that means that about 375 million have been used by people who are now
dead. Accounting for population growth over that time period, where the
population was only about 100 million at inception, and so diving by
about 2, that means that roughly some 200 million numbers have been used
by people who are now dead. That still leaves about 450 million numbers
that have never been used.

Yes, there might need to be an upgrade someday.
Can anyone say Y2K :-) ?