Re: A simple situation shows confusion about basic concepts

From: Ney André de Mello Zunino <zunino_at_undl.org.br>
Date: Tue, 26 Oct 2004 18:39:09 +0000
Message-ID: <2u7uehF281l4cU1_at_uni-berlin.de>


Alan wrote:

> Your original design was _almost_ correct, then. You have a very rare
> situation- a recursive many-to-many relationship. I've never seen it happen
> before.

Hum... That makes me feel a little better about myself, somehow... :)

> Your original design (for historical purposes):
>
> UC (Universal Concept)
> ======================
> uc_id (PK)
> uc
>
> KB (Knowledge Base)
> ===================
> entry_id (PK)
> uc1_id
> uc2_id
> relation_type
>
>
> It only needs a slight change, which I marked with <--- :
>
> UC (Universal Concept)
> ======================
> uc_id (PK)
> uc
>
> KB (Knowledge Base)
> ===================
> entry_id (PK)
> uc1_id (PK) <--- CHANGE to PK
> uc2_id (PK) <--- CHANGE to PK
> relation_type

Make uc1_id and uc2_id part of the primary key? Why? Shouldn't they be foreign keys (notwithstanding the fact that I didn't indicate so in my original post)?

> And actually, you don't really need entry_id- you could just use
> relation_type, but I think you mentioned that originally, and your reason
> for using it is valid.

"relation_type" merely expresses how the first and second UCs of the respective KB entry are associated to each other ("is a", "is part of", "is agent of", etc.). That only has meaning in the context of the application, not the database. This goes to say that the values for that field may and will often repeat across the records. I think I could actually have removed it from my original post.

> Keep in mind that what we have here is not an entity-relation model (ERD),
> we have a relational schema (table design). The modeling on an ERD would be
> different. I can't draw it here, but basically, you have one rectangle (UC)
> and one diamond (KB) connected twice, once in the uc1 role, and once in the
> uc2 role, both with mandatory participation and "many" cardinality. Further,
> you have two attributes on the diamond, entry_id and relation_type.

I will have to look up some documentation in order to take full advantage of your remarks here. But I do understand it when you say there is a difference between table design and ER modelling.

Best regards,

-- 
Ney André de Mello Zunino
Received on Tue Oct 26 2004 - 20:39:09 CEST

Original text of this message