Two depended ManyToMany Releations












0















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 relation 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 relation exists. If it does, get foo with ord=1



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



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






mysql relational-theory







share|improve this question

























  • 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. it seems bar must be joined to foo with constraint of foo.ord=1, not to user...

    – Akina
    18 mins ago


















0















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 relation 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 relation exists. If it does, get foo with ord=1



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



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






mysql relational-theory







share|improve this question

























  • 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. it seems bar must be joined to foo with constraint of foo.ord=1, not to user...

    – Akina
    18 mins ago
















0












0








0








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 relation 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 relation exists. If it does, get foo with ord=1



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



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






mysql relational-theory







share|improve this question
















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 relation 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 relation exists. If it does, get foo with ord=1



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



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






mysql relational-theory




mysql relational-theory






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 1 min ago







Adam

















asked 1 hour ago









AdamAdam

1033




1033













  • 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. it seems bar must be joined to foo with constraint of foo.ord=1, not to user...

    – Akina
    18 mins ago





















  • 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. it seems bar must be joined to foo with constraint of foo.ord=1, not to user...

    – Akina
    18 mins ago



















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. it seems bar must be joined to foo with constraint of foo.ord=1, not to user...

– Akina
18 mins ago







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. it seems bar must be joined to foo with constraint of foo.ord=1, not to user...

– Akina
18 mins ago












1 Answer
1






active

oldest

votes


















0














Schematically:



CREATE TABLE users (id, 

                    name);



CREATE TABLE foo (id, 

                  user_id,  

                  name,  

                  ord,  

                  FK (user_id) REF users (id),  

                  KEY (id, ord));



CREATE TABLE bar (id,  

                  foo_id,  

                  name,  

                  ord AS (1) VIRTUAL,  

                  FK (foo_id, ord) REF foo (id, ord));




share
























  • Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

    – Adam
    1 min ago











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


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227853%2ftwo-depended-manytomany-releations%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














Schematically:



CREATE TABLE users (id, 

                    name);



CREATE TABLE foo (id, 

                  user_id,  

                  name,  

                  ord,  

                  FK (user_id) REF users (id),  

                  KEY (id, ord));



CREATE TABLE bar (id,  

                  foo_id,  

                  name,  

                  ord AS (1) VIRTUAL,  

                  FK (foo_id, ord) REF foo (id, ord));




share
























  • Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

    – Adam
    1 min ago
















0














Schematically:



CREATE TABLE users (id, 

                    name);



CREATE TABLE foo (id, 

                  user_id,  

                  name,  

                  ord,  

                  FK (user_id) REF users (id),  

                  KEY (id, ord));



CREATE TABLE bar (id,  

                  foo_id,  

                  name,  

                  ord AS (1) VIRTUAL,  

                  FK (foo_id, ord) REF foo (id, ord));




share
























  • Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

    – Adam
    1 min ago














0












0








0







Schematically:



CREATE TABLE users (id, 

                    name);



CREATE TABLE foo (id, 

                  user_id,  

                  name,  

                  ord,  

                  FK (user_id) REF users (id),  

                  KEY (id, ord));



CREATE TABLE bar (id,  

                  foo_id,  

                  name,  

                  ord AS (1) VIRTUAL,  

                  FK (foo_id, ord) REF foo (id, ord));




share













Schematically:



CREATE TABLE users (id, 

                    name);



CREATE TABLE foo (id, 

                  user_id,  

                  name,  

                  ord,  

                  FK (user_id) REF users (id),  

                  KEY (id, ord));



CREATE TABLE bar (id,  

                  foo_id,  

                  name,  

                  ord AS (1) VIRTUAL,  

                  FK (foo_id, ord) REF foo (id, ord));





share











share


share










answered 7 mins ago









AkinaAkina

3,6681311




3,6681311













  • Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

    – Adam
    1 min ago



















  • Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

    – Adam
    1 min ago

















Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

– Adam
1 min ago





Sorry I think I was unclear in the title, foo and bar are ManyToMany relations. One foo may also have many users so the pivot tables are necessary.

– Adam
1 min ago


















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














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227853%2ftwo-depended-manytomany-releations%23new-answer', 'question_page');
}
);

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







-

Popular posts from this blog

ف. موراي أبراهام

Image
ف. موراي أبراهام F. Murray Abraham ف. موراي أبراهام في 2008 معلومات شخصية الاسم عند الولادة فريد مراد إبراهيم Fahrid Murray Abraham الميلاد 24 أكتوبر 1939 (العمر 79 سنة) شاكبيتسبرغ، بنسيلفانيا مواطنة الولايات المتحدة  أسماء أخرى فرانك موراي أبراهام Frank Murray Abraham الحياة العملية المدرسة الأم جامعة تكساس في أوستن  التلامذة المشهورين جون كيل [1]   المهنة ممثل تلفزيوني،  وممثل أفلام،  وممثل تعبيري،  وممثل مسرحي  سنوات النشاط 1971–الآن موظف في كلية بروكلين  الجوائز جائزة الأوسكار لأفضل ممثل  (عن عمل:أماديوس)   المواقع IMDB صفحته على IMDB  السينما.كوم صفحته على السينما.كوم  تعديل   ف. موراي أبراهام (بالإنجليزية: F. Murray Abraham ) ، ممثل أمريكي من اصل سوري أرثوذكسي من ناحية الاب وإيطالي من ناحية الام وهو ممثل أمريكي من مواليد 24 أكتوبر 1939، حاصل على جائزة الأوسكار 1984 كأفضل ممثل عن دوره في فيلم أماديوس كما حصل بنفس الدور على جائزة الغولدن غلوب 1985 كأفضل ممثل في فيلم د...
Read more

صرب

Image
صرب التعداد الكلي 11.5–12.5 مليون [a] مناطق الوجود المميزة يبلغ عدد سكانها أكثر 100,000 في يوغوسلافيا السابقة   صربيا (excl. كوسوفو) 5,988,150 (2011)   البوسنة والهرسك 1,086,733 (2013) [1]   الجبل الأسود b 178,110 (2011) [2]   كوسوفو 90–120,000 (2012 قالب:Estimation) a   سلوفينيا 38,964 (2002) [3]   جمهورية مقدونيا 35,939 (2002) [4] بقية أوروبا · ·   ألمانيا بحدود 500,000 (2014 قالب:Estimation)   النمسا بحدود 300,000 (2010 قالب:Estimation) [5]   سويسرا بحدود 71,260 (2015 قالب:Estimation) [6]   فرنسا بحدود 120,000 (2002 قالب:Estimation) [7]   السويد بحدود 110–120,000 (قالب:Estimation)   المملكة المتحدة بحدود 70,000 (2001 قالب:Estimation) [8]   رومانيا 18,076 (2011) [9] أمريكا الشمالية ·   الولايات المتحدة c 199,080 (2012) [10]   كندا c 80,320 (2011) [11] بقية العالم ·   أستراليا c 69,544 (2011) [12] ...
Read more

كأس إنترتوتو

Image
كأس إنترتوتو شعار كأس إنترتوتو معلومات عامة الرياضة كرة القدم انطلقت 1968 (1995 من قبل اليويفا) انتهت 2008 المنظم الاتحاد الأوروبي لكرة القدم عدد الطبعات 35 نسخة رسمية تواترها سنوية عدد المشاركين 50 فريق الموقع الرسمي الموقع الرسمي الأكثر تتويجا هامبورغ شتوتغارت شالكه 04 نادي فياريال (لقبين) التسلسل الزمني للمنافسة تعديل   كأس إنترتوتو (بالإنجليزية: UEFA Intertoto Cup ) هي مسابقة كرة قدم صيفية لأندية كرة القدم الأوروبية التي لم تأهل للبطولات الكبرى المختلفة التي تقام سنوياً تحت رعاية الاتحاد الأوروبي لكرة القدم؛ مثل دوري أبطال أوروبا وكأس الاتحاد الأوروبي سابقاً. وقد أنشئت المسابقة في موسم 1961–62 كبطولة ودية تضم عدة أندية كرة قدم أوروبية، قبل أن يقوم الاتحاد الأوروبي لكرة القدم بإضافتها لقائمة البطولات المعترفة بها في عام 1995. وفي عام 2008 قام الاتحاد الأوروبي لكرة القدم بدمج بطولة كأس إنترتوتو مع بطولة كأس الاتحاد الأوروبي، [1] وبهذا أصبحت كل من بطولة كأس الاتحاد الأوروبي و كأس إنترتوتو وكأس الكؤوس الأوروبية مدموج...
Read more