mySQL Schema design advise -

question: how can use schema below list of users have not signed off on document if have role included document?

background:

i have users table. users have roles. simple setup:

role table:           role_id | name user table:           user_id | name users_roles table:    user_id | role_id 

now need add documents table:

documents table:  document_id | name 

i need know when user has signed off on document:

documents_users table: user_id | document_id 

so far pretty simple.

but gets hard - need add restriction on documents, can have documents assigned multiple roles:

i.e. document_id 1 role_id (3,4,5).

i'm guessing need do

documents_roles table: role_id | document_id 

but - here's hard bit dont know: how can use schema above list of users have not signed off on document if have role included document?

your structure correct:

select users_roles.user_id, documents_roles.document_id documents_roles -- documents -- join documents on (documents.document_id = documents_roles.document_id) -- if need details documents table join users_roles using (role_id) -- users expected sign them -- join users on (users.user_id = users_roles.user_id) -- if need details users table left join documents_users on     documents_users.document_id = documents_roles.document_id ,     documents_users.user_id = users_roles.user_id -- find whether have signed     documents_users.user_id null -- expected users have *not* signed     -- , documents_roles.document_id = some_id ;