I can't make my mind up about this. I have users with different roles. Some are teachers and some are students. What is better? A profile table with all possible attributes as nullable columns or a user meta table that stores each key => value as a new row?
The fields will change depending on the user role so all students will have a grade field and all teachers will have a school field, but a teach will never have a grade field. We may add additional fields in the future, so I'm trying to determine the best solution for this.
Does anyone have experience with something like this?
Honestly i think the best way is to have a student and teacher share one table for authentication. This will store all data necessary for authentication and additional cols for basic data (if you like).
Then use polymorphic one to one relationships to model your needed datastructure. This relation will be called "role" and will associate another Eloquent Class like "Teacher" and "Student" to the "User" table, each storing there own values in a separate table.
This way you will reduce the amount of nullable columns.
You then have to add 2 cols with role_id and role_type to the "User" table.
This way you can extend your roles. For example with "Director" , "Staff" or whatever.
There are a lot of drawback with a table storing key => value pairs.
You dont really define what possible values a Student or Teacher can have.
You will face troubles with accessing these values.
Instead you could just create one column in profile and add an array of key values to this specific column. But then you lose the opportunity to use Mutators. (Same as above)
Hey vogtdominik, thank you for your reply. I am sorry that I wasn't more clear on my current setup. Right now I have a single users table for all users. I then have a roles table that has different roles i.e administrator, teacher, student. I then have a pivot table that connects a role to a user called role_user I also have a profile table that connects to a user profile_user. I definitely like specifying the columns that I want, and rather that then having key value pairs. I am just not sure of the drawbacks.
So you would recommend a profile table with all possible fields as a column?
Yes. Because i feel, that later in the progress, it will feel much more natural, to call $user->address instead of `$user->informations()->where('key', 'address')->first()->value'.
The other solution would have a lot of additional overload, like querying or mapping, which is mostly solved with Eloquent it self.
