Re: A simple situation shows confusion about basic concepts

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 - 03:55:29 CEST