1

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

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

Is this right ?

  • 1
    Can a user be more than one user_type at a time? – Lennart Nov 9 '17 at 20:55
  • @Lennart yes, as I said "A user can have 1 role or more" – rook99 Nov 10 '17 at 3:17
0

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

-1

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

  • EAV is always a bad idea – Neil McGuigan Nov 9 '17 at 19:56
  • @NeilMcGuigan How can it be always a bad idea.Check this stackoverflow.com/a/876459/5671807 and this dbforums.com/… – AnouarZ Nov 9 '17 at 20:07
  • @NeilMcGuigan why is this a bad idea ? I do like it and its more dynamic ... can you explain ? – rook99 Nov 10 '17 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. – Lennart Nov 10 '17 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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