Designing simple database relation (school)
I'm new to designing database relations. Could someone have a look at my relations and see if it makes sense? This is what I've done,
Relations
Membership [one to many] Club
Membership [one to many] Student
Membership [one to many] Positions
Category [one to many] Club
Category [one to many] Position
Description
1) Every student may be in multiple clubs.
2) They hold a position in each of these clubs (e.g. President, Chairman).
3) The positions belong to a particular category (e.g. Sports, Clubs, Student Council).
4) The clubs belong to a particular category as well (e.g. Sports, Clubs, Student Council).
Models
Membership
- clubID
- studentID
- positionID
Club
- id
- name
- categoryID
Student
- id
- name
- class
Position
- id
- name
- description
- points
- categoryID
Category
- id
- name
sql database database-design
asked Nov 22 '18 at 8:43
chopzchopz
771113
|
show 1 more comment
I'm new to designing database relations. Could someone have a look at my relations and see if it makes sense? This is what I've done,
Relations
Membership [one to many] Club
Membership [one to many] Student
Membership [one to many] Positions
Category [one to many] Club
Category [one to many] Position
Description
1) Every student may be in multiple clubs.
2) They hold a position in each of these clubs (e.g. President, Chairman).
3) The positions belong to a particular category (e.g. Sports, Clubs, Student Council).
4) The clubs belong to a particular category as well (e.g. Sports, Clubs, Student Council).
Models
Membership
- clubID
- studentID
- positionID
Club
- id
- name
- categoryID
Student
- id
- name
- class
Position
- id
- name
- description
- points
- categoryID
Category
- id
- name
sql database database-design
asked Nov 22 '18 at 8:43
chopzchopz
771113
2
The one problem I see here is that there is no way to enforce the rule that the position and club should both belong to the same category (which isn't specified in the description but does make sense). In fact, I would argue that "3) The positions belong to a particular category" is redundant - the club already belongs to a category, and a position without a club doesn't make sense.
– Zohar Peled
Nov 22 '18 at 8:53
I would drop the link between positions and categories. Makes little sense. If there is nothing more than the name in the Category table, and if your data volume is low (<10.000 recs) I would probably even drop the Category table (even if academically, it's the right thing.
– Patrick Honorez
Nov 22 '18 at 9:23
@ZoharPeled Thanks for replying. The reason why "position belongs to a particular category" is because upon new creation of club, the form can dynamically populate positions belonging to a particular category. Does that make sense?
– chopz
Nov 22 '18 at 10:03
@PatrickHonorez Thanks for replying. What do you think of the above comment I made?
– chopz
Nov 22 '18 at 10:05
1
The design of your tables should be done to properly represent the reality you deal with, according to Relational Database normalisation rules. Your forms come later in the process and should definitely have no impact on your data structure !! And by the way you can have the source of a combo linked to itself...
– Patrick Honorez
Nov 22 '18 at 10:08
|
show 1 more comment
I'm new to designing database relations. Could someone have a look at my relations and see if it makes sense? This is what I've done,
Relations
Membership [one to many] Club
Membership [one to many] Student
Membership [one to many] Positions
Category [one to many] Club
Category [one to many] Position
Description
1) Every student may be in multiple clubs.
2) They hold a position in each of these clubs (e.g. President, Chairman).
3) The positions belong to a particular category (e.g. Sports, Clubs, Student Council).
4) The clubs belong to a particular category as well (e.g. Sports, Clubs, Student Council).
Models
Membership
- clubID
- studentID
- positionID
Club
- id
- name
- categoryID
Student
- id
- name
- class
Position
- id
- name
- description
- points
- categoryID
Category
- id
- name
sql database database-design
asked Nov 22 '18 at 8:43
chopzchopz
771113
I'm new to designing database relations. Could someone have a look at my relations and see if it makes sense? This is what I've done,
Relations
Membership [one to many] Club
Membership [one to many] Student
Membership [one to many] Positions
Category [one to many] Club
Category [one to many] Position
Description
1) Every student may be in multiple clubs.
2) They hold a position in each of these clubs (e.g. President, Chairman).
3) The positions belong to a particular category (e.g. Sports, Clubs, Student Council).
4) The clubs belong to a particular category as well (e.g. Sports, Clubs, Student Council).
Models
Membership
- clubID
- studentID
- positionID
Club
- id
- name
- categoryID
Student
- id
- name
- class
Position
- id
- name
- description
- points
- categoryID
Category
- id
- name
sql database database-design
sql database database-design
asked Nov 22 '18 at 8:43
chopzchopz
771113
asked Nov 22 '18 at 8:43
chopzchopz
771113
asked Nov 22 '18 at 8:43
chopzchopz
771113
asked Nov 22 '18 at 8:43
chopzchopz
771113
asked Nov 22 '18 at 8:43
chopzchopz
771113
771113
2
The one problem I see here is that there is no way to enforce the rule that the position and club should both belong to the same category (which isn't specified in the description but does make sense). In fact, I would argue that "3) The positions belong to a particular category" is redundant - the club already belongs to a category, and a position without a club doesn't make sense.
– Zohar Peled
Nov 22 '18 at 8:53
I would drop the link between positions and categories. Makes little sense. If there is nothing more than the name in the Category table, and if your data volume is low (<10.000 recs) I would probably even drop the Category table (even if academically, it's the right thing.
– Patrick Honorez
Nov 22 '18 at 9:23
@ZoharPeled Thanks for replying. The reason why "position belongs to a particular category" is because upon new creation of club, the form can dynamically populate positions belonging to a particular category. Does that make sense?
– chopz
Nov 22 '18 at 10:03
@PatrickHonorez Thanks for replying. What do you think of the above comment I made?
– chopz
Nov 22 '18 at 10:05
1
The design of your tables should be done to properly represent the reality you deal with, according to Relational Database normalisation rules. Your forms come later in the process and should definitely have no impact on your data structure !! And by the way you can have the source of a combo linked to itself...
– Patrick Honorez
Nov 22 '18 at 10:08
|
show 1 more comment
2
The one problem I see here is that there is no way to enforce the rule that the position and club should both belong to the same category (which isn't specified in the description but does make sense). In fact, I would argue that "3) The positions belong to a particular category" is redundant - the club already belongs to a category, and a position without a club doesn't make sense.
– Zohar Peled
Nov 22 '18 at 8:53
I would drop the link between positions and categories. Makes little sense. If there is nothing more than the name in the Category table, and if your data volume is low (<10.000 recs) I would probably even drop the Category table (even if academically, it's the right thing.
– Patrick Honorez
Nov 22 '18 at 9:23
@ZoharPeled Thanks for replying. The reason why "position belongs to a particular category" is because upon new creation of club, the form can dynamically populate positions belonging to a particular category. Does that make sense?
– chopz
Nov 22 '18 at 10:03
@PatrickHonorez Thanks for replying. What do you think of the above comment I made?
– chopz
Nov 22 '18 at 10:05
1
The design of your tables should be done to properly represent the reality you deal with, according to Relational Database normalisation rules. Your forms come later in the process and should definitely have no impact on your data structure !! And by the way you can have the source of a combo linked to itself...
– Patrick Honorez
Nov 22 '18 at 10:08
2
2
The one problem I see here is that there is no way to enforce the rule that the position and club should both belong to the same category (which isn't specified in the description but does make sense). In fact, I would argue that "3) The positions belong to a particular category" is redundant - the club already belongs to a category, and a position without a club doesn't make sense.
– Zohar Peled
Nov 22 '18 at 8:53
The one problem I see here is that there is no way to enforce the rule that the position and club should both belong to the same category (which isn't specified in the description but does make sense). In fact, I would argue that "3) The positions belong to a particular category" is redundant - the club already belongs to a category, and a position without a club doesn't make sense.
– Zohar Peled
Nov 22 '18 at 8:53
I would drop the link between positions and categories. Makes little sense. If there is nothing more than the name in the Category table, and if your data volume is low (<10.000 recs) I would probably even drop the Category table (even if academically, it's the right thing.
– Patrick Honorez
Nov 22 '18 at 9:23
I would drop the link between positions and categories. Makes little sense. If there is nothing more than the name in the Category table, and if your data volume is low (<10.000 recs) I would probably even drop the Category table (even if academically, it's the right thing.
– Patrick Honorez
Nov 22 '18 at 9:23
@ZoharPeled Thanks for replying. The reason why "position belongs to a particular category" is because upon new creation of club, the form can dynamically populate positions belonging to a particular category. Does that make sense?
– chopz
Nov 22 '18 at 10:03
@ZoharPeled Thanks for replying. The reason why "position belongs to a particular category" is because upon new creation of club, the form can dynamically populate positions belonging to a particular category. Does that make sense?
– chopz
Nov 22 '18 at 10:03
@PatrickHonorez Thanks for replying. What do you think of the above comment I made?
– chopz
Nov 22 '18 at 10:05
@PatrickHonorez Thanks for replying. What do you think of the above comment I made?
– chopz
Nov 22 '18 at 10:05
1
1
The design of your tables should be done to properly represent the reality you deal with, according to Relational Database normalisation rules. Your forms come later in the process and should definitely have no impact on your data structure !! And by the way you can have the source of a combo linked to itself...
– Patrick Honorez
Nov 22 '18 at 10:08
The design of your tables should be done to properly represent the reality you deal with, according to Relational Database normalisation rules. Your forms come later in the process and should definitely have no impact on your data structure !! And by the way you can have the source of a combo linked to itself...
– Patrick Honorez
Nov 22 '18 at 10:08
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53426910%2fdesigning-simple-database-relation-school%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53426910%2fdesigning-simple-database-relation-school%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
2
The one problem I see here is that there is no way to enforce the rule that the position and club should both belong to the same category (which isn't specified in the description but does make sense). In fact, I would argue that "3) The positions belong to a particular category" is redundant - the club already belongs to a category, and a position without a club doesn't make sense.
– Zohar Peled
Nov 22 '18 at 8:53
I would drop the link between positions and categories. Makes little sense. If there is nothing more than the name in the Category table, and if your data volume is low (<10.000 recs) I would probably even drop the Category table (even if academically, it's the right thing.
– Patrick Honorez
Nov 22 '18 at 9:23
@ZoharPeled Thanks for replying. The reason why "position belongs to a particular category" is because upon new creation of club, the form can dynamically populate positions belonging to a particular category. Does that make sense?
– chopz
Nov 22 '18 at 10:03
@PatrickHonorez Thanks for replying. What do you think of the above comment I made?
– chopz
Nov 22 '18 at 10:05
1
The design of your tables should be done to properly represent the reality you deal with, according to Relational Database normalisation rules. Your forms come later in the process and should definitely have no impact on your data structure !! And by the way you can have the source of a combo linked to itself...
– Patrick Honorez
Nov 22 '18 at 10:08