Online Learning: Different User Roles Different Fields

Question

I'm trying to build an online learning system ( similar to Udemy ), where users have different types ( or roles ).

For now "roles" are : students , instructors and admins ,and it may increase in the future.Where each role has its own permissions.

A user can have 1 role or more, so a user can be a student of some course and an instructor of ( another course )

see below:

the problem : now, each role may have different fields ,for example: an instructor role has a unique field like "biography" which no any of the other user roles has.

how can this be accomplished with this database design , I was thiking of creating tables like:

**instructors_users Table**
user_id
some unique fields for instructors role

**students_users Table**
user_id
some unique fields for students role

Is this right ?

Answer 1

Use Table Inheritance

If you don't mind nulls, use Single Table Inheritance. Add a "type" column, then add nullable columns for the different types, and check constraints for integrity

https://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

if you do mind nulls, add one table per subtype and join to super type with a foreign key

https://www.martinfowler.com/eaaCatalog/classTableInheritance.html

ORMs like Hibernate can automate this for you

Answer 2

Add a table called field which has all fileds (biography, mathematics..)

**field Table**
id
name_fields

And another table called user_field_role

**user_field_role table**
user_id
field_id
role_id

if you want to make a user X instructor of biography then in user_field_role you put the user_id and biography id and instroctor id