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_at_ursa-nb00s0.nbnet.nb.ca>
>
> table,
>
>
> entered
>
>
> cannot
>
>
> having
>
>
> congruent.
>
>
> advice,
>
>
> You are right. We straightened this out in a different subthread.
Date: Wed, 11 Apr 2007 13:18:09 GMT
Message-ID: <le5Th.23276$PV3.230816_at_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 - 15:18:09 CEST