Re: another simple data modelling question

crappy wrote:

> 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.

Ah, then you're NOT representing the same fact twice. Characteristic-personality actually represents the DOMAIN for each personality. For example, the "introvert" personality has the domain of "shy," "quiet" and "modest." An introvert can have any proper subset of these characteristics -- say "quiet" and "modest" but not "shy."

So is it the case that if "shy," "quiet" and "modest" are the characteristics of "introvert" and "Joe" is an "introvert," then "Joe" cannot have characteristics other than "shy," "quiet" and/or "modest?" Is there a minimum number? A quorum necessary for a person to claim a type of personality?

It also seems somewhat of a chicken/egg issue. Is a person identified with a personality, and from there you look to see which of the characteristics he/she posesses? Or is it the other way around -- you identify the characteristics that exist, and from there you look to see which personality type that represents? Without being a psychologist (although my brother is...maybe I should ask him), it seems like the second case is the way it's done in real life.

Regardless, is what we're looking at here actually a ternary relationship?

> 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.

Then forget what I said about how psychologists acutally do it. :-)  

> 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.

Right, not a candidate key, yada yada.

This is off the top of my head, and there's probably a million reasons why this is a bad idea, but I'm not willing to spend the time to think it through, so someone else can critique it.

You have a table personality-characteristic, which identifies the valid characteristics for a given personality.

You have a table person-personality-characteristic, which represents the ternary relationship between the three entities. For example, ("Joe","Introvert","Quiet") is a valid tuple here. The personality & characteristic are a composite foreign key to the personality-characteristic table, which ensures that Joe has a personality & characteristic combination which is valid.

So one table represents the set of combinations which are valid. The other table represents the combinations that a given person actually has. No redundancy. No triggers.

I'd also be curious whether both personality and characteristic are really entities, as opposed to one just being an attribute. But since you said the real problem is in an entirely different subject matter, the point is probably moot.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Fri May 18 2001 - 23:32:41 CEST