Re: A simple situation shows confusion about basic concepts

From: Dan Gidman <danatcofo_at_gmail.com>
Date: 27 Oct 2004 06:25:03 -0700
Message-ID: <1098883503.708613.312020_at_f14g2000cwb.googlegroups.com>


I think what everyone is trying to get at is that you need an associative table. One that will hold a unique relationship between Itself and a discriptive of that relatioship. There can be more than one discriptive of that same relatioship as well. This is really a 3 way relationship. It requires at least two tables. The Universal Concept and the Knowledge Base. In one of my earlier post I misrepresented that because the Knowledge base needs another key in order to be unique. As discribed here with two it would look like this.

UC----------------------
uc_id          PK (autonumber)

uc
KB----------------------
uc_id1        PK,FK
uc_id2        PK,FK
relation       PK

-------------------------

This would correctly represent your concepts. However you do run into data redundancy. If the relation is a string and the KB is a very expansive size there will be alot of repeated data (ie "is" will be in the db in many instances) and make it more time consuming to query on that data. That time may only be picoseconds lol but it can build up pretty quickly considering there would be many more KB entries in comparison to the UC. To make it 3NF (3rd Normal Form) you would break out the relation to a 3rd table which I will call Relation Type (RT) It would then look like this.
UC----------------------
uc_id          PK (autonumber)

uc
KB----------------------
uc_id1        PK,FK
uc_id2        PK,FK
rt_id           PK,FK

-------------------------
RT--------------------- rt_id PK(autonumber)

relation

This layout will create a 3 part concantinated key for KB allowing the actual contents of the real relatioship to be entered into the db 1 and only 1 time. This ensures data integrity as there will only be one spelling of a relation type and that type will become easily searchable through an inner join query. Received on Wed Oct 27 2004 - 15:25:03 CEST

Original text of this message