Mysql – Online Learning: Different User Roles Different Fields

database-designforeign keyMySQLtable

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 ?

Best Answer

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

Related Solutions

Mysql – User roles and permissions

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data. You could have very simple code which interprets each permission strictly at face value. To do an administrative task, the user would need an 'A' permission. To do a publishing task, they would need a 'P' permission, etc. In this case an administrator would need three records per site (one each of A, P, E). Similarly publishers would need two records per site and editors only one. There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site. In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'. The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column. If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.

Mysql – Multiple User Types – DB Design Advice

I would advice 1st one is better approach , which is

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

Reason:-

User related data like columns in students, instructors etc tables may increase in future so it will be easy to manage with 1st approach.

and choosing the other options to create a table with too many numbers of columns which would be not good in terms of future database management and with increasing number of columns in future it will more problematic.

Best Answer

Related Solutions

Mysql – User roles and permissions

Mysql – Multiple User Types – DB Design Advice

Related Question