Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Database design - ERD. Modeling Question

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd just create one table of "Entities" to hold both persons and organizations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Really? 100% different?

    Do they not both have names? Do they not both have addresses? Do then both not have have contact numbers?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2010
    Posts
    3
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •