CODEWITHSUNDEEP
mysqlschema
Laurence
Laurence

Reputation: 60040

mySQL Schema design advise

Question: How can I use the schema below to get a list of users who have NOT signed off on a document IF they have a Role that is included for a document?

Background:

I have a users table. Users have roles. It is a simple setup:

Role table:           Role_ID | Name
User table:           User_ID | Name
Users_Roles table:    User_ID | Role_ID

Now I need to add a Documents table:

Documents table:  Document_ID | Name

I need to know when a user has signed off on a document:

Documents_Users table: User_ID | Document_ID

So far its pretty simple.

But now it gets hard - I need to add a restriction on documents, so I can have documents assigned to multiple roles:

i.e. Document_ID 1 is for Role_ID (3,4,5).

I'm guessing I need to do

Documents_Roles table: Role_ID | Document_ID

But - here's the hard bit I dont know: How can I use the schema above to get a list of users who have NOT signed off on a document IF they have a Role that is included for a document?

Upvotes: 3

Views: 298

Answers (1)

RandomSeed
RandomSeed

Reputation: 29749

Your structure is correct:

SELECT Users_Roles.User_ID, Documents_Roles.Document_ID
FROM Documents_Roles -- get some documents
-- JOIN Documents ON (Documents.Document_ID = Documents_Roles.Document_ID) -- if you need details from the Documents table
JOIN Users_Roles USING (Role_ID) -- get all users expected to sign them
-- JOIN Users ON (Users.User_ID = Users_Roles.User_ID) -- if you need details from the Users table
LEFT JOIN Documents_Users ON
    Documents_Users.Document_ID = Documents_Roles.Document_ID AND
    Documents_Users.User_ID = Users_Roles.User_ID -- find whether they have signed it
WHERE
    Documents_Users.User_ID IS NULL -- only those expected users who have *not* signed it
    -- AND Documents_Roles.Document_ID = some_id
;

Upvotes: 1

Related Questions