Hello

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.

Hopefully that makes sense.

Many many thanks in advance

I'd just create one table of "Entities" to hold both persons and organizations.

Cheers Blindman,

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.

Really? 100% different?

Do they not both have names? Do they not both have addresses? Do then both not have have contact numbers?

Hey Blindman,

Sorry I didn't mean to come across quite like I think I did. No not 100% different you're right, I think its more my understanding of my own entities and the roles they both play in the ERD.

Anyway I'll certainly take your idea on board. Many thanks again for the response.

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.