Re: Table acting as a relation table

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:Ar4Th.2221$Q23.914_at_newssvr17.news.prodigy.net...
>
> "David Cressey" <cressey73_at_verizon.net> wrote in message
> news:9A%Sh.5550$Lm.1310_at_trndny05...
> >
> > "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.
> >
>
> I just want to point out that while it is true that it's bad *general*
> advice, there are times when it is necessary--mainly to work around the
> limitations of a particular DBMS.

A very good point. That is why I included the word "general" in my post.

It's often the case in this newsgroup (and, I suspect, in others) that we give very specific advice as if it were general advice. And often we do so using non verbalized assumptions about the situation facing the advice seeker. These assumptions often don't agree with the actual circumstances the advice seeker faces. In the past, Bob Badour has had a few choice words to say on this subject. If you'll search Google groups on "Badour crank" you should get a fair sample.

While I don't quite see eye to eye with Bob on this, I have to agree with his general premise: that in order to give actual specific advice on a given problem, one would have to know a great deal more about the actual circumstance than is typically communicated in newsgroup interchanges. Received on Wed Apr 11 2007 - 14:54:39 CEST