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.

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

PersonId (pk)

TeacherId (Pk)
PersonId (Fk)

StudentId (Pk)
PersonId (fk)

commented: 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.