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: If no bar bar relationship exists, get foo with smallest ord column.



Is there maybe a more convenient database structure for this scenario?
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.