Unique ID

[Stanley Sinclair]

I have a medical records database. At this moment one server.
Providence willing, and if things go well for me, I may have 9 other
servers join in.

Each patient will have, by GENERATE_UNIQUE(), a unique ID from the 10
servers, but each server identifies its own patients by a sequential
number.

Someone goes to the database and asks to see the record of
PATIENTNUMBER 100. There will be ten results, because everyone has a
PATIENTNUMBER 100. The intent is that the user will choose the right
one.

However, the very fact that there *exists* a record for anyone other
than the patient whom that user has the right to see violates HIPAA
law.

That is, should there be N PATIENTNUMBER 100's, then N-1 would be
other than unique to the user's query. The user is not allowed to
even know of the existence of the N-1 patients.

Penalties are in the tens and hundreds of thousands of dollars and
prison time!

What to do?

[Jan M. Nelken]

Stanley Sinclair wrote:

I have a medical records database. At this moment one server.
Providence willing, and if things go well for me, I may have 9 other
servers join in.

Each patient will have, by GENERATE_UNIQUE(), a unique ID from the 10
servers, but each server identifies its own patients by a sequential
number.

Someone goes to the database and asks to see the record of
PATIENTNUMBER 100. There will be ten results, because everyone has a
PATIENTNUMBER 100. The intent is that the user will choose the right
one.

However, the very fact that there *exists* a record for anyone other
than the patient whom that user has the right to see violates HIPAA
law.

That is, should there be N PATIENTNUMBER 100's, then N-1 would be
other than unique to the user's query. The user is not allowed to
even know of the existence of the N-1 patients.

Penalties are in the tens and hundreds of thousands of dollars and
prison time!

What to do?

What happens when you create id by concatenating value of CURRENT
SERVER and result of GENERATE_UNIQUE()?

Jan M. Nelken

[Serge Rielau]

Stanley,

What speaks against using a natural key, like the patients social
insurance number or the health insurance ID?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jan M. Nelken]

Stanley Sinclair wrote:

I have a medical records database. At this moment one server.
Providence willing, and if things go well for me, I may have 9 other
servers join in.

Each patient will have, by GENERATE_UNIQUE(), a unique ID from the 10
servers, but each server identifies its own patients by a sequential
number.

Someone goes to the database and asks to see the record of
PATIENTNUMBER 100. There will be ten results, because everyone has a
PATIENTNUMBER 100. The intent is that the user will choose the right
one.

However, the very fact that there *exists* a record for anyone other
than the patient whom that user has the right to see violates HIPAA
law.

That is, should there be N PATIENTNUMBER 100's, then N-1 would be
other than unique to the user's query. The user is not allowed to
even know of the existence of the N-1 patients.

Penalties are in the tens and hundreds of thousands of dollars and
prison time!

What to do?

Modify application such that at startup it calls sqleseti() API - which
allows to set client workstation name, accounting string, application
name etc etc.

See cli_info.c (or cli_info.cxx - depending on your believes).

Use some substring of CURRENT CLIENT_WRKSTNNAME special register
concatenated with result of GENERATE_UNIQUE() call to have id which
could be unique across multiple machines.

One example which guarantees uniqueness would be to get MAC address of
the network card inside routine issuing sqleseti() call and store this
MAC address as workstation name.

Jan M. Nelken

[Mark A]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@hanover.torolab.ibm.com...

Stanley,

What speaks against using a natural key, like the patients social
insurance number or the health insurance ID?

Cheers
Serge
--
Serge Rielau

That may not work. I heard about this identical problem myself years ago,
but not sure how they decided to handle it (pre HIPA laws).

The problem is the many poor people who seek free medical attention at
clinics in public hospitals. The illegal immigrants without a social
security number are issued id cards with a unique number to track the
patient, but the cards get shared by many other illegal aliens who are
afraid to tell the hospital their real names and afraid to get their own id
cards (with a unique number). This is true even though the hospitals do not
ever turn them in to immigration.

What happens is that when the doctor pulls up their medical records on the
computer (or even paper files), they are really composite records of many
different people who used the same id card, and it drives the docs crazy. So
Juan Gonzalez with a unique id that he is issued (in lieu of a social
security number) has just about every known medical ailment on his record.
They have waste a lot of time figuring out what their medical history is to
make sure they get the right diagnosis and treatment. Of course the public
hospitals are already under-funded, and this makes things even worse.

[Rhino]

This sounds like a prime candidate for a biometric solution.

It would be insane to send someone like Stanley to jail for failing to
distinguish between umpteen people who share the same card. It is insane to
expect the doctors to distinguish between umpteen people who share the same
card and to keep a usable patient history in this situation.

The real problem here is the card sharing and that's what needs to get
addressed. That won't be solved by database design but it may be solved by
using biometrics: if the patient's photograph (or fingerprints or a retina
scan) are taken when the card is issued and the same biometric is used to
verify that the patient and the card match when the service (medical
treatment) is given, you have a chance to keep a realistic medical history
and the doctor finally has a fighting chance to treat the patient
appropriately.

I don't know whether the appropriate governments allow use of biometrics in
ID cards but, if not, I don't see any realistic hope of solving this
problem. Then there will be a political fight as the politicians responsible
for health systems try to get people to see that this is the only reasonable
solution, then duking it out in court with the people who will inevitably
see biometrics as Big Brother and resist their use. In 10 or 20 years, this
might get settled, assuming the necessary 'political will' exists.
Otherwise, the idea of keeping medical history for shared cards is a farce
that will be perpetuated, which will mean that people like Stanley live in
fear that they will go to jail for things over which they have no control
and doctors will live in fear that they will kill patients, either because
they ignored obviously shared medical histories or because they *didn't*
ignore them.

Rhino

"Mark A" <[e-mail]> wrote in message
news:Ww*****************@news.uswest.net...

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@hanover.torolab.ibm.com...

Stanley,

What speaks against using a natural key, like the patients social
insurance number or the health insurance ID?

Cheers
Serge
--
Serge Rielau
That may not work. I heard about this identical problem myself years ago,
but not sure how they decided to handle it (pre HIPA laws).

The problem is the many poor people who seek free medical attention at
clinics in public hospitals. The illegal immigrants without a social
security number are issued id cards with a unique number to track the
patient, but the cards get shared by many other illegal aliens who are
afraid to tell the hospital their real names and afraid to get their own

id cards (with a unique number). This is true even though the hospitals do not ever turn them in to immigration.

What happens is that when the doctor pulls up their medical records on the
computer (or even paper files), they are really composite records of many
different people who used the same id card, and it drives the docs crazy. So Juan Gonzalez with a unique id that he is issued (in lieu of a social
security number) has just about every known medical ailment on his record.
They have waste a lot of time figuring out what their medical history is to make sure they get the right diagnosis and treatment. Of course the public
hospitals are already under-funded, and this makes things even worse.