Leonardo Leonardo - 6 months ago 9
MySQL Question

Issues on designing SQL users table

I'm creating a database that has a

users
table, but there are three types of users (admin, teachers and students) and some types has its own properties. Here are my solutions:

1 - Three different tables:

table_admin
id
name
email
password

table_teachers
id
name
email
password
teacher_only_a
teacher_only_b

table_students
id
name
email
password
student_only_a
student_only_b


2 - One table, allowing NULL values:

table_users
id
name
email
password
teacher_only_a (null on admin and student)
teacher_only_b (null on admin and student)
student_only_a (null on admin and teacher)
student_only_b (null on admin and teacher)


3 - Related tables:

table_users
id
name
email
password
teacher_id (null on admin and student)
student_id (null on admin and teacher)

table_teachers
id
teacher_only_a
teacher_only_b

table_students
id
student_only_a
student_only_b


Which are the best design option? Is there another solution?

Answer

Why not

table_users
  id
  name
  email
  password
  is_admin

table_teachers
  user_id
  teacher_only_a
  teacher_only_b

table_students 
  user_id
  student_only_a
  student_only_b

That would abstract out the user information so there isn't any redundancy.