Re: Table acting as a relation table

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 11 Apr 2007 12:28:04 GMT
Message-ID: <ov4Th.23255$PV3.230678@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.

Remember, he is using the join table to model a 1:N relative cardinality because he cannot alter either T_HOSPITAL or T_CLINIC. Ordinarily, I think he would simply add a HOSP_CODE to T_CLINIC. I think you are getting thrown off because the normal use of a join table is to model a N:M relative cardinality, which would require a compound key. Received on Wed Apr 11 2007 - 07:28:04 CDT