Re: Table acting as a relation table

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 11 Apr 2007 13:18:09 GMT
Message-ID: <le5Th.23276$PV3.230816@ursa-nb00s0.nbnet.nb.ca>

David Cressey wrote:

> "Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
> news:ov4Th.23255$PV3.230678_at_ursa-nb00s0.nbnet.nb.ca...
>

>>David Cressey wrote:
>>
>>
>>>"Marshall" <marshall.spight_at_gmail.com> wrote in message
>>>news:1176248821.606596.203310_at_l77g2000hsb.googlegroups.com...
>>>
>>>
>>>>On Apr 10, 3:32 pm, "Guy" <guh..._at_yahoo.com> wrote:
>>>>
>>>>
>>>>>I am going to rephrase my question:
>>>>>
>>>>>T_HOSPITAL <-------------- T_HOSPITAL_CLINIC <-------------> T_CLINIC
>>>>> HOSP_CODE                    HOSP_CODE
>>>>>CLINIC_CODE
>>>>>                                         CLINIC_CODE
>>>>>
>>>>>With my asssociation table I can define which clinic belongs to which
>>>>>hospital. Someone told me that such table cannot have the only two
>>>>>field HOSP_CODE, CLINIC_CODE, but some other "key" field should be
>>>>>there. Quote "a table with no key to identify each row should not be
>>>>>modeled".
>>>>>
>>>>>Obviously my table will work as is. Is there however a rule I am
>>>>>enfringing ?
>>>>
>>>>If the association table T_HOSPITAL_CLINIC has only the
>>>>two attributes, HOSP_CODE and CLINIC_CODE, that would
>>>>not be breaking any rules. (Assuming you use the other
>>>>constraints as in Bob's earlier message.) If that table already
>>>>has a key (CLINIC_CODE) then you WOULD be breaking a
>>>>rule if you added an additional key just to make your "someone"
>>>>happy.
>>>>
>>>>It is true that every table should have a key.
>>>>
>>>>These things are NOT true:
>>>>
>>>> Every table should have a key that is unique to that table
>>>> Every key should be a single column
>>>> Every key should be an integer
>>>>
>>>>Sometimes people who have learned how to do things
>>>>by rote mistakenly end up believing some of the above.
>>>>
>>>>Marshall
>>>
>>>What Marshall said, and also this:
>>>
>>>If you make (HOSP_CODE,CLINIC_CODE) the key to the T_HOSPITAL_CLINIC

>
> table,
>

>>>this would have the effect of preventing the same entry from being

>
> entered
>

>>>twice into this table.  This is generally a "good thing".  Relations

>
> cannot
>

>>>have duplicate tuples, by definition.  By preventing the table from

>
> having
>

>>>duplicate rows,  you cause the behavior of the table to be more

>
> congruent.
>

>>>I would take your expert's advice to create separate key column for each
>>>table with a very large grain of salt.  I believe that's bad general

>
> advice,
>

>>>and I think Marshall and Bob are also saying that.
>>
>>A nit: According to Guy's requirements, (HOSP_CODE,CLINIC_CODE) is
>>reducible to (CLINIC_CODE) because a clinic gets associated to at most
>>one hospital.

>
> You are right. We straightened this out in a different subthread.

Yeah, I saw that after I sent the post. Sorry. Received on Wed Apr 11 2007 - 08:18:09 CDT