Database design - ERD. Modeling Question

views:

110

answers:

+2 Q:

Database design - ERD. Modeling Question

Hello

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

Organisation
Person
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

+1 A:

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.

Justin K 2010-07-26 15:22:22

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

FairFunk 2010-07-26 15:35:39

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.

Justin K 2010-07-26 15:51:18

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

FairFunk 2010-07-27 08:20:32

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

Justin K 2010-07-27 12:03:56

alt text

EDIT

Well, this should work too: alt text

Damir Sudarevic 2010-07-26 16:53:01

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.

FairFunk 2010-07-26 17:05:07

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.

Adam Musch 2010-07-27 02:56:37

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)

FairFunk 2010-07-27 08:23:37

ansaurus

tags:

views:

answers:

Database design - ERD. Modeling Question

related questions