Re: Table acting as a relation table

"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. For example, Sql Server does not implement FOR EACH ROW triggers. As a consequence, UPDATE triggers cannot handle changes that affect both a key column and more than one row. A surrogate key can work around this limitation by guaranteeing that the values for at least one key will remain constant throughout *any* update.

>
>
>
>
>
>
Received on Wed Apr 11 2007 - 14:24:24 CEST