0

I design an academic information registration system. I have 3 types of users namely teachers, students and operators. As all these have some properties in common I thought of designing it in the following manner.

teacher(id, profile_id)
student(id, profile_id)
operator(id, profile_id)
profile(id, first_name, last_name, tel)
teacher_specific(teacher_id, academic_qual)

- teacher_specific has details which are specific to the teacher apart from the common attributes.

I thought of designing like above instead of normal procedure described below as it seemed better to have common details at a single place and sometimes a teacher also needs to be treated as a student. So, in above design data will not be duplicated.

teacher(id, first_name, last_name, tel, academic_qual)
student(id, first_name, last_name, tel)
operator(id, first_name, last_name, tel)

I feel comfortable with the secondly mentioned design as it's the usual procedure. But I'm not sure about pros and cons of both when compared.

It's highly appreciated if somebody can give me pros and cons of both mentioned above comparatively and recommend the better one.

Thanks in advance.

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by chandimak
1

The former is a better design however Profile should really be Person and you don't need two teacher tables

Person
PersonId (pk)
LastName
FirstName
Tel

Teacher
TeacherId (Pk)
PersonId (Fk)
TeacherQuals

Student
StudentId (Pk)
PersonId (fk)

Votes + Comments
Well explained.
0

Thank you very much Chris. It was of great help. I didn't realize that it doesn't require 2 tables for teacher in the first place. I'll go ahead with this design.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.