Re: another simple data modelling question

crappy wrote:

> let's say you have 3 entities: person, characteristic, and personality, and
> let's say each one has a integer primary key and a text description field for
> simplicity.
>
> now let's say that person is a child of personality. 1 person has exactly one
> personality, one personality can be had by 0 or many persons.
>
> now, suppose characteristics and personality are many:many, so you make a
> junction
>
> characteristic_personality {
> characteristic_id,
> personality_id
> }
>
> with appropriate foreign keys of course. so this table is saying that only
> certain characteristics apply to certain personalities.
>
> now suppose person and characteristic are many:many as well, so you do the same
> kind of junction table between these two.
>
> person_characteristic {
> person_id,
> characteristic_id
> }
>
> blah blah.
>
> now my problem is this: how do you ensure that person/characteristic pairs are
> valid with respect to the characteristic/personality pairs, *as well as* the
> implied personality of each person (via the child-parent relationship between
> person and personality)?
>
> in other words, with the given setup, it is possible to give a person a
> characteristic (in person_characteristic) that isn't 'allowed' for that person's
> personality.

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 - 21:45:57 CEST