another simple data modelling question

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.

i know that's kind of confusing but from reading your other posts i know you're all smart people. thanks in advance. Received on Thu May 17 2001 - 15:50:12 CDT