Results 1 to 4 of 4

Thread: Design Problem

  1. #1
    Join Date
    May 2003
    Posts
    3

    Red face Design Problem with Subtype tables

    I am in the design phase of a relational database using OO methods...I have three groups of people that I want to track...1. Students 2. Parents 3. Adult Volunteers...

    I have started with a table called people where I have attributes that are common to all three groups of people...then I have a students table and volunteer table that have attributes common only to a student or volunteer...the key in these tables is also the FK of the people table. I think this is proper design.

    Here is my question:

    There is a many-many relationship with parents and students. A third junction table is needed I know but how is this accomplished with subtype tables...would I create the juntion between parents table and Students table or between parents table and people table?

    Any suggestion would be appreciated. Maybe this is all wrong and someone has a better solution...thanks

    Tony
    Last edited by tbaker95; 05-09-03 at 22:23.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Design Problem with Subtype tables

    Originally posted by tbaker95
    I am in the design phase of a relational database using OO methods...I have three groups of people that I want to track...1. Students 2. Parents 3. Adult Volunteers...

    I have started with a table called people where I have attributes that are common to all three groups of people...then I have a students table and volunteer table that have attributes common only to a student or volunteer...the key in these tables is also the FK of the people table. I think this is proper design.

    Here is my question:

    There is a many-many relationship with parents and students. A third junction table is needed I know but how is this accomplished with subtype tables...would I create the juntion between parents table and Students table or between parents table and people table?

    Any suggestion would be appreciated. Maybe this is all wrong and someone has a better solution...thanks

    Tony
    As stated, the relationship should be between the Parents and Students tables. But are these really subtypes? They sound more like roles. I have been a student, and am now a parent and could also be an adult volunteer at the same time. Your model doesn't permit this.

  3. #3
    Join Date
    May 2003
    Posts
    3
    andrewst:

    I think you are right...I might be making this more complicated than I need to...however...there are certain attributes that are specific to students and certain attributes specific to parents and volunteers...and relationships betwen the different "roles"

    Are you saying that I need one table for all and assign a role attribute...leave the unrelated attributes null that don't apply for the record?

    I appreciate your help...where I am stumped is the generalization and the many to many relationship between the two subclasses. If the only relationship between parents and students is that they are both people then fine but with the m-m between student and parents???

    Possibly a second table that tracked the relationships between the different roles?

    This table would only have two columns that tracked the two ID's of the people relationship.

    As you can tell this sort of has me going around and around trying to find the best solution for the problem at hand.

    Thanks
    Tony

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by tbaker95
    andrewst:

    I think you are right...I might be making this more complicated than I need to...however...there are certain attributes that are specific to students and certain attributes specific to parents and volunteers...and relationships betwen the different "roles"

    Are you saying that I need one table for all and assign a role attribute...leave the unrelated attributes null that don't apply for the record?

    I appreciate your help...where I am stumped is the generalization and the many to many relationship between the two subclasses. If the only relationship between parents and students is that they are both people then fine but with the m-m between student and parents???

    Possibly a second table that tracked the relationships between the different roles?

    This table would only have two columns that tracked the two ID's of the people relationship.

    As you can tell this sort of has me going around and around trying to find the best solution for the problem at hand.

    Thanks
    Tony
    If you want a m:m relationship between Students and Parents, then an intersection table between Students and Parents would be correct.

    If you wanted to track various types of relationship like Student-Parent, Student-Student, Student-Teacher, then you might want a more generic Person-Person intersection table with a relationship type column.

    If you can keep it specific, then querying the data and enforcing rules is easier. A generic solution gives more flexibility, and reduces the number of tables, but makes querying and rule enforcement harder. There is no single, correct answer; it is a matter of considering the alternatives and adopting the most appropriate for your needs.

Posting Permissions

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