Re: Table acting as a relation table

"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. Received on Wed Apr 11 2007 - 08:51:49 CEST