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:

enter image description here

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:

1. Query: Check if bar relationship exists. If it does, get foo with ord=1

1. Query: If no bar bar relationship exists, get foo with smallest ord column.

Is there maybe a more convenient database structure for this scenario?

edited 4 mins ago

MDCCL

6,70731744

asked 12 hours ago

Adam

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 with ord=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:

enter image description here

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.

1. Query: Check if bar relationship exists. If it does, get foo with ord=1

1. Query: If no bar bar relationship exists, get foo with smallest ord column.

Is there maybe a more convenient database structure for this scenario?

edited 4 mins ago

MDCCL

6,70731744

asked 12 hours ago

Adam

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 with ord=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:

enter image description here

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.

1. Query: Check if bar relationship exists. If it does, get foo with ord=1

1. Query: If no bar bar relationship exists, get foo with smallest ord column.

Is there maybe a more convenient database structure for this scenario?

edited 4 mins ago

MDCCL

6,70731744

asked 12 hours ago

Adam

1033

I have a user who may have 0 or more foo's and bar's. Here is a diagram:

enter image description here

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.

1. Query: Check if bar relationship exists. If it does, get foo with ord=1

1. Query: If no bar bar relationship exists, get foo with smallest ord column.

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

Adam

1033

edited 4 mins ago

MDCCL

6,70731744

asked 12 hours ago

Adam

1033

edited 4 mins ago

MDCCL

6,70731744

edited 4 mins ago

MDCCL

6,70731744

edited 4 mins ago

MDCCL

6,70731744

asked 12 hours ago

Adam

1033

asked 12 hours ago

Adam

1033

asked 12 hours ago

Adam

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 with ord=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 with ord=1. Its possible that a user has 10 bars but only 3 foos.

– Adam
8 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

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.

edited 10 hours ago

answered 10 hours ago

Akina

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

minusnine

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

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.

edited 10 hours ago

answered 10 hours ago

Akina

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.

edited 10 hours ago

answered 10 hours ago

Akina

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.

edited 10 hours ago

answered 10 hours ago

Akina

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.

edited 10 hours ago

answered 10 hours ago

Akina

3,7031311

edited 10 hours ago

answered 10 hours ago

Akina

3,7031311

answered 10 hours ago

Akina

3,7031311

answered 10 hours ago

Akina

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

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

minusnine

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

minusnine

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

minusnine

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

minusnine

356

answered 3 hours ago

minusnine

356

answered 3 hours ago

minusnine

356

answered 3 hours ago

minusnine

356

add a comment |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xrhrft