Database design - ERD. Modeling Question

Question

Hopefully someone may be able to quickly advise the best way for me to model my ERD (I'm using IDEF1X & ERWin not Chen Notation) although it doesn't really matter.

Basically I have Three Entities

1. Organisation
2. Person
3. Trustee

Business rules state that a Trustee can be either a person or organisation.

My question is, would it be correct to have two primary, foreign key relationships to Trustee. One from Organisation > Trustee and the second from Person > Trustee. Where one of the FK fields of Trustee will always be NULL depending on if the Trustee is a person or organisation.

Or would it be better to have some sort of associative table between person and organisation (person_organisation) and create a PK, FK relationship from person_Organisation > Trustee.

The second option seems better as there might be other entities that also need to link to both Organisation and person.

Hopefully that makes sense.

Many many thanks in advance

Antofagasta

Answer 1

EDIT

Well, this should work too:

Rockery

Hi Damir, thats great. I did think about the supertype subtype method but I've also got a few other entities that can be either an organisation or person which aren't related enough to roll up into one generic supertype for example Trustee, stakeholder and shareholder. – Antofagasta

Sounds to me like those entities like Stakeholder and Shareholder are really Roles that either a Person or Organization can fulfill, as in Justin K's example. – Teodora

Damir, many thanks for the ERD it always seems so obvious once someone has done it for you. Very much appreciated! :) I'll probably go with this solution and see how things pan out (might even name a attribute after you if all goes well! lol) – Antofagasta

Answer 2

It seems to me that Trustee is just one example of a role a person can play in an organisation. So really, we're talking about a three-way association Person_Organization_Role where all three are the primary key. Now, that could be broken down into Person_Organization > PO_Role, but my preference is not to take things that far.

Action

Hello Justin. Many thanks for the response. Would your solution still work if a person was not assoisated with any organisation? – Antofagasta

The actual table would be (person_id, org_id, role_id), so you would still have a Person table, and that could have rows not referenced in the association. – Action

Cheers for the advice. I'm obviously still learning so any solutions such as yours are very much appreciated. – Antofagasta

My pleasure. And now that I see Damir's solution, I realized I solved the wrong problem. :P – Action