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 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?

  • 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ᵀᴹ Jan 23 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. – Adam Jan 23 at 14:18
1

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. – Adam Jan 23 at 14:40
  • @Adam Foo and bar are in general independet. It is critical. Add this info into a question text. Additionally: does a user can have multiple bars? if so, does each bar must have its own foo with ord=1, or it's enough to have one such foo for to accept any count of bars? – Akina Jan 24 at 4:52
  • 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. – Adam Jan 24 at 7:44
  • @Adam Oh... it's bidirectional check... but MySQL ignores CHECK constraints... so the only way is to use trigger logic. Q1: If you want to delete user reference to a foo with ord=1, you will delete all references to all bars for this user, or you will eject this foo reference deletion until all bars references deleted explicitely? Q2: Maybe better way is to realize all logic as a server one (as stored procs) restricting direct (by query) edition of these tables? – Akina Jan 24 at 9:32
  • 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. – Adam Jan 24 at 12:21
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.