Design Question

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

Tonypost what the current fields are in each table and the relationships between them.|||You have identified that Students, Parents and Volunteers are all types of people, so you have created a People table with a primary key (PeopleID). In the Student and Voluteer tables there are keys that contain PeopleID.

The task is to show the relationship between a student and its parents. Students and Parents are all sub-types of People. What you are trying to define is a relationship from one person to another, so the design should link a record from the People table to another record in the People table.

The Parent table will have a PeopleID column that represents the student and a second column with a renamed PeopleID to represent the ID of the parent. You can then add any other columns that contain parent data. The primary key will be a compound key based on the first two columns.

Referencing column 1 will list all the people who are the parents of Student A. Referencing column 2 will list all the students of parent B.

By linking people records in this way you can also represent the relationship where a parent is also a student.

As the table can be used to represent any relationship between two people records, you may want to check if any other relationships need to be represented (Student A is the brother/sister of Student B etc.) If so, you could add a RelationshipID column to the table, add it to the primary key and change the name of the table.

Whether this is the best solution for you depends on all of your requirements. Hope you found this useful.