Re: A simple situation shows confusion about basic concepts

From: <danatcofo_at_gmail.com>
Date: 26 Oct 2004 07:05:06 -0700
Message-ID: <1098799506.542882.182640_at_c13g2000cwb.googlegroups.com>


Hello Ney,

It seems to me that you are trying to model the binary relationships between real entities. These are things that can exsist as a concept or name without the others because we already understand their relationships. You seem to be trying to model that understanding. Well your original layout look almost right. I think you made a mistake in your KB though. It should probably be like this

UC



uc_id PK
uc

KB


uc_id1        PK,FK
uc_id2        PK,FK

relationshipType

This would create 2 - 1:n relationships between UC and KB

Something that I would also consider is adding another table to this database to allow for easy searching and comparing with the engine. consider this.

KB


uc_id1        PK,FK
uc_id2        PK,FK
relation_id   PK,FK
-------------------------

RT



relation_id PK
relation

Now also just a little note. I always prefer to use an autonumber as the primary key withing a db. This is to allow the database to get as large as it needs to without rewrite or porting anything. This is just me though and it is certanly not universal.

Cheers

Dan Gidman

Kostas wrote:
> Hello Ney,
>
> I am a novice only, so don't overweigh my opinion, but still, I got
some
> help out of this group and I d like to help others if I could.
>
> 1. Your case seems to me to be a reflexive association or
relationship. Sort
> of like the Person is an Employer of another person. Since the type
of
> relation may change as your design indicates, we have a reflexive
> association with a relation_type attribute. This is how it would be
in the
> conceptual model, which pretty much translates to the logical or
physical
> model the way you designed it. The kb entry has 2 foreign keys, both
> originating from the primary key of the UC entity. There is no reason
why
> this wouldnt be correct. Reflexive associations rely on this idea.
>
> Note: If you are trying to build complex hierarchies that go down
several
> levels then I strongly suggest you google "hierarchies with SQL" or
> something to that effect. This is something I need to investigate
myself
> also as some of my data includes hierarchies. The matter appears to
be a bit
> more complicated than I initially thought, and actually there is a
book out
> from Celko discussing such issues.
>
> 2. Now to your choice of an autonumber field versus natural field.
This is a
> hot topic with many proponents and opponents on both sides, most of
who are
> experts. So, basically, go figure. I say, experiment and see what
works best
> for you. I am trying to go with natural keys when there are readily
> available, but at othertimes they are not that obvious, or they just
are not
> there. So my tables have mixed primary keys, some natural, some
autonumbers.
> My first observation is that natural keys increase the size of your
database
> because their indexing take more space and also because of their
migration
> to other tables (i.e., a FK with 4 or 8 for an autonumber bytes
versus one
> with 200 for a text field). If your database is huge this might be a
concern
> for you. In my case, 35MB vs 50MB is not a big worry so I ll keep the

> natural keys for the moment until I am convinced they are the way to
go or
> not.
>
> Cheers!
>
> Konstantinos
>
>
> "Ney André de Mello Zunino" <zunino_at_inf.ufsc.br> wrote in message
> news:2u55lqF25f27iU1_at_uni-berlin.de...
> > Hello.
> >
> > Even though I have designed and worked on some projects involving
> > databases before, I realize my understanding of the concepts still
needs a
> > lot of improvement. After a relatively long period of inactivity, I
find
> > myself struggling to comprehend and get the design of a current
project
> > right. In particular, I am having trouble with a pair of entities
and
> > their interrelationship.
> >
> > The first entity represents what is called, in the context of the
project,
> > a /universal concept/. Think of it as a unique string which
identifies a
> > real concept such as a rock, a car, love, peace, etc. There I had
my first
> > dilemma: should I make that very string the primary key of my
entity or
> > should I use an artificial key such as "concept_id"? Because the
strings
> > can potentially become very long and cumbersome, I chose the latter

> > option. All right, let's move on.
> >
> > The second entity represents entries in a knowledge base or a
conceptual
> > graph. Those entries establish binary relationships between pairs
of
> > /universal concepts/, the entity previously described.
Schematically, the
> > entities look something like:
> >
> > UC (Universal Concept)
> > ======================
> > uc_id (PK)
> > uc
> >
> > KB (Knowledge Base)
> > ===================
> > entry_id (PK)
> > uc1_id
> > uc2_id
> > relation_type
> >
> > So, basically, the KB is a collection of entries which represent
binary
> > connections of different types between UCs.
> >
> > Now for my questions (please, bear with me if some of them sound
stupid):
> >
> > 1) While reading about the different ER relation types on the
> > documentation of the modelling tool, I was having a hard time
choosing
> > among /identifying/, /non-identifying/ and /m:n/ relationships. Who
is
> > dependent and who is independent in the situation presented? Is it
correct
> > to think that the KB entity is dependent on the other because an
entry
> > needs universal concepts (two actually) to exist? In that case,
what is
> > the proper way of relating the entities UC and KB?
> >
> > 2) Are the /uw1_id/ and /uw2_id/ attributes in KB foreign keys? Is
it
> > correct to say that, whenever the primary key of an entity appears
as an
> > attribute on another entity, it is a foreign key? In other
modelling
> > environments with which I have worked, I was able to directly link
an
> > attribute such as /uw1_id/ to its corresponding primary key on the
> > associated entity. With the present tool [1], however, the way to
achieve
> > that seems not so straightforward (of course, this is inherent to
my lack
> > of knowledge both of the tool and the theory). In other words, the
foreign
> > keys are created by establishing relationships, not explicitly. In
the
> > present context, that means I had to set up two relationships
between the
> > entities: one for the /uw1_id/ attribute and another for the
/uw2_id/ one.
> > I am not sure, but somehow, that doesn't seem right.
> >
> > I would be very grateful if someone would care to help me clarify
my
> > confusion with the above issues.
> >
> > [1] CASE Studio 2.17
> >
> > Thank you very much,
> >
> > --
> > Ney André de Mello Zunino
Received on Tue Oct 26 2004 - 16:05:06 CEST

Original text of this message