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?

> I have a table of patient data.
> The patient has a patient ID which is the primary key.

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

> The table also has a foreign key which is the hospital that that
> patient is enrolled in.

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

> 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)

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=A0pm, Gordon Burditt <gor...@hammy.burditt.org> wrote:
> > I have a table of patient data.
> > The patient has a patient ID which is the primary key.
>
> If two patients can have the same patient ID, then the patient ID
> by itself is NOT a primary key.
>
> > The table also has a foreign key which is the hospital that that
> > patient is enrolled in.
>
> Is that *THE* hospital, as in exactly one, or can there be more than
> one or less than one?
>

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.

> > 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)
>
> No. =A0The combination of the two should be set as unique, as in:
> alter table patients add primary key (patientID, hospitalID);
>
> > How is this handled properly?

SpreadTooThin says...

> 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.

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:

> 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?

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:
> SpreadTooThin says...
>
>> 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.
>
> 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.

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/en/miscellaneous-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
--