# 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:

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?

• If a user has a bar, is that associated with a specific foo? Can a user have 10 bars but only 3 foos? Commented Jan 23, 2019 at 13:49
• @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.
Commented Jan 23, 2019 at 14:18

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.

• 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.
• A user can have multiple bars. Sorry for not being clear enough, this is what I meant with `I have a user who may have 0 or more foo's and bar's.`. The condition is that the user should have one foo with ord=`1` to accept any count of bars. It would also not be possible that a user has multiple foo's with ord=`1` because ord is unique.
• to Q1,Q2: at the moment I do not insert any foo references for a user with `ord=1`. I handle it with server logic. So if I want all foo's from the user, I get all foo's that are stored as related in the DB and then I check if the user has any bar's, and if he has any, I add the foo with `ord= 1` to the foo collection of the user. Its acceptable, but I was wondering if there is maybe a more elegant solution to this.
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