Hopefully somebody might be able to quickly advise on the best way for me to model my ERD.
Basically I have Three Entitiys
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.
Another example would be shareholder i.e a shareholder can be either a organisation or person at any given time.
The only issue with that is although an organisation and person have the same roles in this instance in other relationships they are 100% different. i.e. a person makes up the board but an organisation does not.
I also thought about a subtype / supertype relationship with Trustee as the supertype and organisation / person as subtypes but decided against it because shareholder and stakeholder would also need to be supertypes.
Would the idea of Organisation_Person table be trouble waiting to happen? Obviously there would be the potential for a lot nulls.
There's more than one way to skin a cat, and there's more than one way to design a database schema. Just tossed mine out there for your consideration, as I have had success with it in the past.
Logical data structures are not always the best method of implementing physical data structures. Often, your physical model will be more abstract.
If it's not practically useful, then it's practically useless.