Online Learning: Different User Roles Different Fields
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 ?
mysql database-design foreign-key table
asked Nov 9 '17 at 16:58
rook99rook99
214
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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 ?
mysql database-design foreign-key table
asked Nov 9 '17 at 16:58
rook99rook99
214
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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 ?
mysql database-design foreign-key table
asked Nov 9 '17 at 16:58
rook99rook99
214
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 ?
mysql database-design foreign-key table
mysql database-design foreign-key table
asked Nov 9 '17 at 16:58
rook99rook99
214
asked Nov 9 '17 at 16:58
rook99rook99
214
edited Nov 9 '17 at 17:47
rook99
asked Nov 9 '17 at 16:58
rook99rook99
214
asked Nov 9 '17 at 16:58
rook99rook99
214
asked Nov 9 '17 at 16:58
rook99rook99
214
214
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
add a comment |
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
1
1
Can a user be more than one user_type at a time?
– Lennart
Nov 9 '17 at 20:55
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
@Lennart yes, as I said "A user can have 1 role or more"
– rook99
Nov 10 '17 at 3:17
add a comment |
2 Answers
2
active
oldest
votes
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
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
add a comment |
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
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f190556%2fonline-learning-different-user-roles-different-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
add a comment |
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
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
add a comment |
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
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
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
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
answered Nov 9 '17 at 19:55
Neil McGuiganNeil McGuigan
5,51932344
5,51932344
add a comment |
add a comment |
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
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
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
add a comment |
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
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
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
add a comment |
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
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
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
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
answered Nov 9 '17 at 18:05
AnouarZAnouarZ
1176
1176
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
add a comment |
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
EAV is always a bad idea
– Neil McGuigan
Nov 9 '17 at 19:56
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 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
@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
@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
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f190556%2fonline-learning-different-user-roles-different-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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