0

I have a user table with a self-referential relationship between instructors and students. Currently students are related to their instructors by way of an instructor_id foreign_key. A student will always have an instructor and an instructor will never be a student.

With all that said, in terms of authorization would it be OK to simply determine if a User is a student if they have a value for their instructor_id field and vice-versa? Or should I explicitly be defining user roles?

Noz
  • 6,216
  • 3
  • 47
  • 82

1 Answers1

2

Firstly, I'd try to find some way to make it not self referential. Perhaps by making just a persons table and having a separate table be the link (users.instructor_id linking to instructors which has the fields instructor_id and user_id, the instructors.instructor_id being used in users.instructor_id, and then you could query SELECT * FROM users WHERE EXISTS (SELECT * FROM instructors WHERE instructors.user_id = users.user_id) to get a list of current instructors), or even best case have a students table and an instructors table.

If you are sure that no instructor will have an instructor_id, then I believe you should be able to check if a user is a student by that field being null(or a default of something like a -1).

I would suggest rebuilding your table structure to be non-self-referential, it will help you in the long run when cases such as an instructor being assigned to another instructor, or a student teaching for some reason or another.

Spencer Cole
  • 1,122
  • 1
  • 10
  • 20
  • Instructors belonging to instructors and students being represented as instructors won't be something I'll be catering for as that would introduce a lot of unnecessary complexity to an extremely rare occurrence for my particular problem domain. As far as my question was concerned, I know it can be done that way (as you stated checking for default) what I want to know is if it SHOULD be done that way, or more to the point is there another way I should be doing it. – Noz Oct 01 '12 at 21:11
  • No, it shouldn't be done this way. The first part of it is two ways to make it not self referential. If it were me, I'd rebuild it with a users table, a instructors table(containing a reference to the users, and also important information relevant to instructors), and a student's table(containing a reference to users, and also important information relevant to students. – Spencer Cole Oct 02 '12 at 12:27
  • Oh I understand what you're saying now, I like the idea of decoupling but I don't know if that will quite work for my problem domain just because both instructors and students share a similar data structure and the only way they differ is in terms of authorization. You did answer my question though, implied roles shouldn't be used. – Noz Oct 02 '12 at 16:02