Re: Table acting as a relation table

"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. Received on Wed Apr 11 2007 - 14:55:53 CEST