Please, a valid DDL to enforce the following rules:

A Party must be a either Person or an Organization
Some Roles can be played by a Party (both Person and Organization)
Some Roles can be played only by a Person
Some Roles can be played only by an Organization

Thanks, Fred

On Jan 11, 9:24*am, frednik... (AT) yahoo (DOT) com wrote:
Please do your own homework.

If you want help, show us what you tried and where you are having
trouble. We will not do it for you, but try to guide you toward a
solution.

HTH,
ed

On Jan 12, 2:31*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
This is what I tried:

Party with 2 exclusive subtypes (Person, Organization) --> 3 entities
Role with 2 non-exclusive subtypes (Person Role, Organization Role) --
The following rules hold:

- Some Roles can be played by a Party (both Person and Organization);
- Some Roles can be played only by a Person;
- Some Roles can be played only by an Organization;
- A Person can play more than a Role;
- An Organization can play more than a Role;

I'd like to enforce correctness by construction (e.g. no triggers).

If Party >---< Role (m:n relationship) then: an Organization can play
a Person Role and vice versa

If Person >---< Person Role and Organization >---< Organization Role
then: you can't subtype Person and Organization using the Role as
discriminator (you would have 2 identical subtypes in case of a non-
exclusive Role type)

If you subtype Role with 3 exclusive subtypes (Person Role,
Organization Role and Common Role) then: still problems... (just try)

Thanks

Fred

Some Roles can be played only by a Person
Some Roles can be played only by an Organization <

I guess the most natural way to model this would be to have separate
tables for Persons and Organziations and also Person_Roles and
Organization_Roles. Then it is easy to create a junction table between
them in order to define the relationships.

Keeping this separate would also give you advantage if you would like
to go one step further and define relationships between Persons and
Organizations. E. g. a person can be an employee of an organization.

brgds

Philipp Post