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.