Yesterday's post dealt with Len Silverston's Party Role model and how to take it to a physical model using ER/Studio. After re-reading the post, I realized that while a party may have a role that distinguishes it as a person rather than an organization such as "Employee," a party might also have a role that is indistinguishable such as "Customer." So if I have a PARTY ROLE with a ROLE TYPE of "Customer," how do I know whether this customer is an individual or an organization if I have some business rule that must handle an order for one in a different way than the for the other?

The answer, I believe, is in adding a PARTY TYPE which tells me what "sub type" the party is but does not tell me what role the party may play in the system. This distinguishes a "type" from a "role" in that the party can only be one type but might play one or more roles in the system at the same time or at different times. Here's what I ended up with (see update 01/04/09 for corrected diagram):

In this way, I can always determine through query what sub-type the party is regardless of the role. Of course, I could perhaps deal with this by having roles that uniquely apply to a specific sub-type but I think that would defeat the purpose of Len's model.

I'd like to hear what you think.

Update: Browsing Len's Volumne 1 on data modeling, I discovered that this is indeed covered in the Party Role model on page 440. And Karen is right about the relationship. I was too eager to post up the image and so overlooked the fact that I'd created the wrong type of relationship between the PARTY TYPE and PARTY entities in ER/Studio. I'll post up a fix soon.

Update 2 (01/04/09): Here's the corrected diagram with the non-identifying relationship. See Karen's comment.

Yeah, that looks better.