Re: Table acting as a relation table

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 10 Apr 2007 21:35:02 GMT
Message-ID: <aqTSh.23100$PV3.228357@ursa-nb00s0.nbnet.nb.ca>

Guy wrote:

> On Apr 10, 4:53 pm, Lennart <erik.lennart.jons..._at_gmail.com> wrote:
> 

>>Guy wrote:
>>
>>[...]
>>
>>
>>>So I added a relation table:
>>>T_HOSPITAL_CLINIC
>>> HOSPITAL_CODE
>>> CLINIC_CODE
>>
>>>T_HOSPITAL --------------> T_HOSPITAL_CLINIC --------------> T_CLINIC
>>
>>>But some old modeling architect revising my model told me that it was
>>>not a good practive to add a table which do not have a key for each of
>>>its records.I would like to know why ? Any other comments
>>>appreciated.
>>>Thanks.
>>
>>Given your design the same clinic can be related to several hospitals,
>>is that intentional?
>>
>>/Lennart

> 
> 
> A clinic can be in any hospital, but only if its not already assigned
> to another hospital. My physical example could be misleading. I am
> dealing with administrative regroupings, rather than real physical
> entities.

I think what you have would make more sense if you redrew it as:

T_HOSPITAL <-------------- T_HOSPITAL_CLINIC <-------------> T_CLINIC

T_HOSPITAL key (HOSPITAL_CODE)
T_CLINIC key (CLINIC_CODE)
T_HOSPITAL_CLINIC key (CLINIC_CODE)

   , foreign key (HOSPITAL_CODE) references T_HOSPITAL (HOSPITAL_CODE)    , foreign key (CLINIC_CODE) references T_CLINIC (CLINIC_CODE) Received on Tue Apr 10 2007 - 16:35:02 CDT