Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Toronto, ON, Canada

    Person hierarchy

    Just thought I'd run this by people here as a sanity check.

    I have a system where we have a table PERSON that contains one entry per person. There is a PersonID column that is the primary key.

    A person can be a student, a person can be an instructor. Or both. (And in the future that list will expand)

    I decided to tackle this by creating two tables - STUDENT_DETAILS and INSTRUCTOR_DETAILS - which use PersonID as their primary key column.

    I then created two views - STUDENT and INSTRUCTOR - that retrieve all fields (including the PERSON fields). So people can write queries like "Give me all students" without having to do the joins explicitly with the PERSON table.

    Any other recommendations/criticism? I loosely based this on DB2's Object-Relational extensions, they allow typed tables and inheritance that mimic the Object-Oriented world.

    Jonathan Petruk
    DB2 Database Consultant

  2. #2
    Join Date
    Apr 2004
    Toronto, Canada
    That seems like a pretty good way of doing it.


  3. #3
    Join Date
    Aug 2004
    Sounds like you stumbled upon a subtype.

Posting Permissions

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