Two Tables using one Enquiry Table?

  • Thread starter Thread starter Paul H
  • Start date Start date
P

Paul H

I have three tables called tblStudents, tblTeachers and tblEnquiries.

tblStudents = StudentID, DOB, Address, etc...
tblTeachers = TeacherID, DOB, Address, etc...


If I just wanted an Enquiries table for Students, creating the fields would
be easy:

tblEnquiries = EnqDate, StudentID, EnqDescription, EnqFollowupDate

Likewise if I just wanted an Enquiries table for Teachers, creating the
fields would be easy:

tblEnquiries = EnqDate, TeacherID, EnqDescription, EnqFollowupDate


But I need a single enquiry table to manage enquiries from both students and
teachers. How do I do this?

Thanks,

Paul
 
The simple method is to have only one table for students and teachers but
have a field to designate which they are. In some cases you may have a
teacher that is also a student. So, if that is possible then maybe use a
number field and display on your form as an option group as Student, Teacher,
and Student/Teacher using 1, 2, or 3..
 
tblStudents = StudentID, DOB, Address, etc...
tblTeachers = TeacherID, DOB, Address, etc...
Click to expand...

Based on this it looks like Students and Teachers have the same
characteristics.

tblPeople = PeopleID, DOB, Address, etc...
tblEnquiries = EnqDate, PeopleID, EnqDescription, EnqFollowupDate

as for the differentiation between teachers and students I have two
ways...

First way, same as your currrent, this way a person can be a teacher
as well as a student.
tblStudents = StudentID, PeopleID
tblTeachers = TeacherID, PeopleID

Or, this method will put all the types of people into a table and then
you can assign as specific type to a person.
tblPeopleType = PeopleTypeID, PeopleTypeName
tblPeople = PeopleID, PeopleTypeID, DOB, Address, etc...

Cheers,
Jason Lepack
 
Back
Top