Modelling two dependent many-to-many relationships
I have a user who may have 0 or more foo's and bar's. Here is a diagram:
Each foo has a unique integer ord.
If a user has a bar, then this implies that he has a foo with ord=1. A user may not have foo with ord=1 without a bar.
My thought how to avoid logical inconsistencies is to simple never insert any foo_user relationship with ord=1. However when I want to query the foo of a user with smallest ord I need to do this a bit complicated like this:
- Query: Check if bar relationship exists. If it does, get foo with
ord=1
- Query: Check if bar relationship exists. If it does, get foo with
- Query: If no bar bar relationship exists, get foo with smallest
ordcolumn.
- Query: If no bar bar relationship exists, get foo with smallest
Is there maybe a more convenient database structure for this scenario?
mysql database-design erd
edited 4 mins ago
MDCCL
6,70731744
asked 12 hours ago
AdamAdam
1033
add a comment |
I have a user who may have 0 or more foo's and bar's. Here is a diagram:
Each foo has a unique integer ord.
If a user has a bar, then this implies that he has a foo with ord=1. A user may not have foo with ord=1 without a bar.
My thought how to avoid logical inconsistencies is to simple never insert any foo_user relationship with ord=1. However when I want to query the foo of a user with smallest ord I need to do this a bit complicated like this:
- Query: Check if bar relationship exists. If it does, get foo with
ord=1
- Query: Check if bar relationship exists. If it does, get foo with
- Query: If no bar bar relationship exists, get foo with smallest
ordcolumn.
- Query: If no bar bar relationship exists, get foo with smallest
Is there maybe a more convenient database structure for this scenario?
mysql database-design erd
edited 4 mins ago
MDCCL
6,70731744
asked 12 hours ago
AdamAdam
1033
3
If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos?
– ypercubeᵀᴹ
9 hours ago
@ypercubeᵀᴹ well they are associated in the sense, that if a user has a bar he must have the unqiue foo withord=1. Its possible that a user has 10 bars but only 3 foos.
– Adam
8 hours ago
add a comment |
I have a user who may have 0 or more foo's and bar's. Here is a diagram:
Each foo has a unique integer ord.
If a user has a bar, then this implies that he has a foo with ord=1. A user may not have foo with ord=1 without a bar.
My thought how to avoid logical inconsistencies is to simple never insert any foo_user relationship with ord=1. However when I want to query the foo of a user with smallest ord I need to do this a bit complicated like this:
- Query: Check if bar relationship exists. If it does, get foo with
ord=1
- Query: Check if bar relationship exists. If it does, get foo with
- Query: If no bar bar relationship exists, get foo with smallest
ordcolumn.
- Query: If no bar bar relationship exists, get foo with smallest
Is there maybe a more convenient database structure for this scenario?
mysql database-design erd
edited 4 mins ago
MDCCL
6,70731744
asked 12 hours ago
AdamAdam
1033
I have a user who may have 0 or more foo's and bar's. Here is a diagram:
Each foo has a unique integer ord.
If a user has a bar, then this implies that he has a foo with ord=1. A user may not have foo with ord=1 without a bar.
My thought how to avoid logical inconsistencies is to simple never insert any foo_user relationship with ord=1. However when I want to query the foo of a user with smallest ord I need to do this a bit complicated like this:
- Query: Check if bar relationship exists. If it does, get foo with
ord=1
- Query: Check if bar relationship exists. If it does, get foo with
- Query: If no bar bar relationship exists, get foo with smallest
ordcolumn.
- Query: If no bar bar relationship exists, get foo with smallest
Is there maybe a more convenient database structure for this scenario?
mysql database-design erd
mysql database-design erd
edited 4 mins ago
MDCCL
6,70731744
asked 12 hours ago
AdamAdam
1033
edited 4 mins ago
MDCCL
6,70731744
asked 12 hours ago
AdamAdam
1033
edited 4 mins ago
MDCCL
6,70731744
edited 4 mins ago
MDCCL
6,70731744
edited 4 mins ago
MDCCL
6,70731744
6,70731744
asked 12 hours ago
AdamAdam
1033
asked 12 hours ago
AdamAdam
1033
asked 12 hours ago
AdamAdam
1033
1033
3
If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos?
– ypercubeᵀᴹ
9 hours ago
@ypercubeᵀᴹ well they are associated in the sense, that if a user has a bar he must have the unqiue foo withord=1. Its possible that a user has 10 bars but only 3 foos.
– Adam
8 hours ago
add a comment |
3
If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos?
– ypercubeᵀᴹ
9 hours ago
@ypercubeᵀᴹ well they are associated in the sense, that if a user has a bar he must have the unqiue foo withord=1. Its possible that a user has 10 bars but only 3 foos.
– Adam
8 hours ago
3
3
If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos?
– ypercubeᵀᴹ
9 hours ago
If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos?
– ypercubeᵀᴹ
9 hours ago
@ypercubeᵀᴹ well they are associated in the sense, that if a user has a bar he must have the unqiue foo with
ord=1. Its possible that a user has 10 bars but only 3 foos.– Adam
8 hours ago
@ypercubeᵀᴹ well they are associated in the sense, that if a user has a bar he must have the unqiue foo with
ord=1. Its possible that a user has 10 bars but only 3 foos.– Adam
8 hours ago
add a comment |
2 Answers
2
active
oldest
votes
Schematically:
CREATE TABLE users (id,
name,
PK (id));
CREATE TABLE user_foo (user_id,
foo_id,
PK (user_id, foo_id),
FK (user_id) REF users (id),
FK (foo_id) REF foo (id));
CREATE TABLE foo (id,
name,
ord,
PK (id),
KEY (id, ord));
CREATE TABLE foo_bar (foo_id,
ord AS (1) VIRTUAL,
bar_id,
PK (bar_id, foo_id),
FK (foo_id, ord) REF foo (id, ord),
FK (bar_id) REF bar (id));
CREATE TABLE bar (id,
name,
PK (id));
One foo may also have many users so the pivot tables are necessary.
Taken into account.
foo and bar are ManyToMany relations
Taken into account.
answered 10 hours ago
AkinaAkina
3,7031311
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
add a comment |
Assuming you aren't missing any relationships between foo and bar, then I think you may be mixing your entity-relationship model with your business logic's restrictions.
An user has many foos, many bars, and vice versa. So far, so good. Checking wether a foo with ord=1 exists or not is your db engine's responsibility, not your e-r model's
answered 3 hours ago
minusnineminusnine
356
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%2f227853%2fmodelling-two-dependent-many-to-many-relationships%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
Schematically:
CREATE TABLE users (id,
name,
PK (id));
CREATE TABLE user_foo (user_id,
foo_id,
PK (user_id, foo_id),
FK (user_id) REF users (id),
FK (foo_id) REF foo (id));
CREATE TABLE foo (id,
name,
ord,
PK (id),
KEY (id, ord));
CREATE TABLE foo_bar (foo_id,
ord AS (1) VIRTUAL,
bar_id,
PK (bar_id, foo_id),
FK (foo_id, ord) REF foo (id, ord),
FK (bar_id) REF bar (id));
CREATE TABLE bar (id,
name,
PK (id));
One foo may also have many users so the pivot tables are necessary.
Taken into account.
foo and bar are ManyToMany relations
Taken into account.
answered 10 hours ago
AkinaAkina
3,7031311
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
add a comment |
Schematically:
CREATE TABLE users (id,
name,
PK (id));
CREATE TABLE user_foo (user_id,
foo_id,
PK (user_id, foo_id),
FK (user_id) REF users (id),
FK (foo_id) REF foo (id));
CREATE TABLE foo (id,
name,
ord,
PK (id),
KEY (id, ord));
CREATE TABLE foo_bar (foo_id,
ord AS (1) VIRTUAL,
bar_id,
PK (bar_id, foo_id),
FK (foo_id, ord) REF foo (id, ord),
FK (bar_id) REF bar (id));
CREATE TABLE bar (id,
name,
PK (id));
One foo may also have many users so the pivot tables are necessary.
Taken into account.
foo and bar are ManyToMany relations
Taken into account.
answered 10 hours ago
AkinaAkina
3,7031311
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
add a comment |
Schematically:
CREATE TABLE users (id,
name,
PK (id));
CREATE TABLE user_foo (user_id,
foo_id,
PK (user_id, foo_id),
FK (user_id) REF users (id),
FK (foo_id) REF foo (id));
CREATE TABLE foo (id,
name,
ord,
PK (id),
KEY (id, ord));
CREATE TABLE foo_bar (foo_id,
ord AS (1) VIRTUAL,
bar_id,
PK (bar_id, foo_id),
FK (foo_id, ord) REF foo (id, ord),
FK (bar_id) REF bar (id));
CREATE TABLE bar (id,
name,
PK (id));
One foo may also have many users so the pivot tables are necessary.
Taken into account.
foo and bar are ManyToMany relations
Taken into account.
answered 10 hours ago
AkinaAkina
3,7031311
Schematically:
CREATE TABLE users (id,
name,
PK (id));
CREATE TABLE user_foo (user_id,
foo_id,
PK (user_id, foo_id),
FK (user_id) REF users (id),
FK (foo_id) REF foo (id));
CREATE TABLE foo (id,
name,
ord,
PK (id),
KEY (id, ord));
CREATE TABLE foo_bar (foo_id,
ord AS (1) VIRTUAL,
bar_id,
PK (bar_id, foo_id),
FK (foo_id, ord) REF foo (id, ord),
FK (bar_id) REF bar (id));
CREATE TABLE bar (id,
name,
PK (id));
One foo may also have many users so the pivot tables are necessary.
Taken into account.
foo and bar are ManyToMany relations
Taken into account.
answered 10 hours ago
AkinaAkina
3,7031311
edited 10 hours ago
answered 10 hours ago
AkinaAkina
3,7031311
answered 10 hours ago
AkinaAkina
3,7031311
answered 10 hours ago
AkinaAkina
3,7031311
3,7031311
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
add a comment |
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
Thank you for this proposal. However its possible that 2 users have the same foo but different bars. Foo and bar are in general independet. They only have the logical implication described in the OP.
– Adam
8 hours ago
add a comment |
Assuming you aren't missing any relationships between foo and bar, then I think you may be mixing your entity-relationship model with your business logic's restrictions.
An user has many foos, many bars, and vice versa. So far, so good. Checking wether a foo with ord=1 exists or not is your db engine's responsibility, not your e-r model's
answered 3 hours ago
minusnineminusnine
356
add a comment |
Assuming you aren't missing any relationships between foo and bar, then I think you may be mixing your entity-relationship model with your business logic's restrictions.
An user has many foos, many bars, and vice versa. So far, so good. Checking wether a foo with ord=1 exists or not is your db engine's responsibility, not your e-r model's
answered 3 hours ago
minusnineminusnine
356
add a comment |
Assuming you aren't missing any relationships between foo and bar, then I think you may be mixing your entity-relationship model with your business logic's restrictions.
An user has many foos, many bars, and vice versa. So far, so good. Checking wether a foo with ord=1 exists or not is your db engine's responsibility, not your e-r model's
answered 3 hours ago
minusnineminusnine
356
Assuming you aren't missing any relationships between foo and bar, then I think you may be mixing your entity-relationship model with your business logic's restrictions.
An user has many foos, many bars, and vice versa. So far, so good. Checking wether a foo with ord=1 exists or not is your db engine's responsibility, not your e-r model's
answered 3 hours ago
minusnineminusnine
356
answered 3 hours ago
minusnineminusnine
356
answered 3 hours ago
minusnineminusnine
356
answered 3 hours ago
minusnineminusnine
356
356
add a comment |
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%2f227853%2fmodelling-two-dependent-many-to-many-relationships%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
3
If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos?
– ypercubeᵀᴹ
9 hours ago
@ypercubeᵀᴹ well they are associated in the sense, that if a user has a bar he must have the unqiue foo with
ord=1. Its possible that a user has 10 bars but only 3 foos.– Adam
8 hours ago