Re: another simple data modelling question

crappy wrote:
>
> thanks for the replies.
>
> i'm not sure if i understand .. wouldn't adding personality_id to the person
> table denormalize it, since it would then have to inherit personality_id from
> characteristic_personality?

The person table already contains personality_id. It would be a denormalization indeed, because you have the personality_id in person and in person_characteristic. But I don't know how to escape the denormalization in your design.

> also, in case i wasn't clear in my first post, i don't want to have a person to
> have *all* the characteristics that are allowed for his personality_type, just
> some of them. so characteristic_personality lists which characteristics are
> 'allowed' for which types, and characteristic_person lists which persons have
> which characteristics.

With my proposal, you don't have to relate all characteristic_personality rows to a person. You only relate those you want.

> the trigger would work, but i wanted to see if there was a way to do it thru RI

You don't exactly need a trigger to do it. You can set up RI at least to ensure that only rows of characteristic_personality are used in person_characteristic. You'll only have to make sure that the personality_ids match.

> In article <3B04B8FE.59761A1_at_racon-linz.at>, Heinz Huber says...
> >
> >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.

I'm not completely sure, how I would model your case. It seems that you have to accept some denormalization.

Perhaps some experts could jump in on this one.

hth,
Heinz Received on Sun Jul 22 2001 - 01:24:33 CEST