Re: another simple data modelling question

crappy wrote:
>
> greetings all.
>
> i have this stupid problem that is stumping me. there must be a simple answer,
> but ..
>
> 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.

From what I read, I understand that a person is only allowed to have the characteristics that are specified for it's personality. If this assumption is true, I wouldn't relate person and characteristic (table person_characeristic above). I would relate person directly to characteristic_personality and ensure that the personality_ids match (automaticly, if you promote personality_id to a part of the key in person). This way you can also set up RI.

hth,
Heinz Received on Fri May 18 2001 - 07:54:06 CEST