Re: another simple data modelling question

From: crappy <nospam_at_newsranger.com>
Date: Fri, 18 May 2001 20:57:09 GMT
Message-ID: <F0gN6.11$d65.310_at_www.newsranger.com>


thanks for your reply. i understand what you're saying. but i think my post was confusing now that i read it again.

the characteristic_personality junction lists what characteristics make sense for which personalities. the reason for the person_characteristic junction is to list what characteristics a person *actually* has. i don't necessarily want to say that a person of such-and-such personality automatically has *all* the characteristics that make sense for that personality type. to follow your example, i want to be able to say that "Joe" is an "introvert," but i don't want that to necessarily mean that he's "shy." However, i do want to be able to say that Joe has the, i dunno, "quiet" characteristic, provided that "introvert"/"quiet" is a valid pair. i know it doesn't make that much sense with this example, but the tables i laid out are actually a simplification of something else, of an entirely different subject matter.

so far, without using triggers, the only way i could think of doing this is to make the relationship between Person and Personality an identifying one, so that personality_id migrates to Person's primary key. this way Person_Characteristic can in turn inherit person_id + personality_id. of course, it's also bad because it violates the rule of using the smallest key necessary to uniquely identify the row.

does that make sense? well, thanks in advance for any advice.

In article <3B057BF5.1630_at_assist.org>, Larry Coon says...
>
>
>A basic premise is that your design should represent a "fact"
>once and only once. Your design with person, personality and
>characteristic already represents the fact that, for example,
>person "Joe" has characteristic "shy" because "joe" is
>related to personality "introvert" and "introvert" is related
>(through the junction table characteristic-personality) to
>characteristic "shy." So adding another junction table
>person-characteristic redundantly represents facts that are
>already represented (assuming a person cannot have a
>characteristic outside the context of a personality). And
>where facts are represented redundantly, they can be
>represented inconsistently.
>
>Without adding the person-characteristic table you can already
>tell which characteristics a person has, and which people have
>a given characteristic. What else are you trying to do with
>your data that you think your design doesn't allow?
>
>
>Larry Coon
>University of California
>larry_at_assist.org
>and lmcoon_at_home.com
Received on Fri May 18 2001 - 22:57:09 CEST

Original text of this message