An example of my dilemma:

I have a table of patient data.
The patient has a patient ID which is the primary key.
The table also has a foreign key which is the hospital that that
patient is enrolled in.
Two patients in the same hospital can not have the same Patient ID.
But two patients can have the same patient ID in different hospitals.

In MySQL workbench should both the patient ID and hospital be set as
Unique (UQ)

How is this handled properly?

If two patients can have the same patient ID, then the patient ID
by itself is NOT a primary key.

Is that *THE* hospital, as in exactly one, or can there be more than
one or less than one?

No. The combination of the two should be set as unique, as in:
alter table patients add primary key (patientID, hospitalID);

> How is this handled properly?

On Mar 29, 4:38*pm, Gordon Burditt <gor... (AT) hammy (DOT) burditt.org> wrote:
I guess the same human could be enrolled in more than one hospital,
but I'm not interested
in ensuring that they have the same or differnt patient IDs in diffent
hospitals.
but a patient must have a hospital.

SpreadTooThin says...

Call me a cynic, but ...

If this was a real-world design, I'd run a book on how long it would be
before someone decides that they want the facility to transfer a patient
(complete with existing data attributes) from one hospital to another.

The "natural" uniqueness here is person and hospital, as per the advice
you have already received. Then you can use the composite key of
person+hospital for a patient record.

Depending on the scope of the data, I'd even be of a mind to allow for
granularity where a patient might have multiple discrete admissions to a
single hospital ...

Geoff M

Am 29.03.2011 23:41, schrieb SpreadTooThin:

In your case, the primary key would not be the patientID alone, but the
combined hospitalID *and* patientID. It's very common to have a) PKs,
that span more than one column and it's also very common to have a FK,
which is also a part of the PK. An example, IMHO easy to understand, is
an invoice with it's positions:

You create an invoice, which has a unique ID (maps your hospital).
You have positions (maps your patient) on this invoice, which alwasy
start with 1 again. But a position alwasy belongs to exactly *one*
invoice, and it normally also cannot be moved to another invoice later.

So you have the ID of the invoice as FK in your position. But the
invoice's ID is also a part of the PK of the position.

But what you eventually missed in your description: is it also necessary
to have *one* patient, which can be a patient in different hospitals but
has always a different patientID in each hospital? Then you it would be
a n:m-relation, where another patientID alone is the PK of the patient.
And you need another table, where you store the relation between a
patient and a hospital, extended by a field that stores the patientID of
the patient in each hospital.

Helmut

El 30/03/2011 3:13, Geoff Muldoon escribió/wrote:
I absolutely agree. This looks like a typical use case for UUIDs:

mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'

http://dev.mysql.com/doc/refman/5.1/...functions.html




--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--