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_at_ursa-nb00s0.nbnet.nb.ca>
>>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
Date: Tue, 10 Apr 2007 21:35:02 GMT
Message-ID: <aqTSh.23100$PV3.228357_at_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 - 23:35:02 CEST