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:

enter image description here

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**
some unique fields for instructors role

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

Is this right ?


2 Answers 2


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


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


ORMs like Hibernate can automate this for you


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

**field Table**

And another table called user_field_role

**user_field_role table**

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

  • EAV is always a bad idea Commented Nov 9, 2017 at 19:56
  • @NeilMcGuigan How can it be always a bad idea.Check this stackoverflow.com/a/876459/5671807 and this dbforums.com/…
    – AnouarZ
    Commented Nov 9, 2017 at 20:07
  • @NeilMcGuigan why is this a bad idea ? I do like it and its more dynamic ... can you explain ?
    – rook99
    Commented Nov 10, 2017 at 4:51
  • @rook99, there are situations where it might be appropriate with EAV, but in most situations, EAV will cause both performance issues as well as difficulties expressing even trivial logic. Commented Nov 10, 2017 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.