db entries visibility (design and select help)

[jogol]

hi experts,

i need to design a mysql db with users and projects. besides other infos, the projects table holds info on wether the project is

• visible to all users
• visible to users located in one or more geographical regions
• visible only to selected users

for example a project can be visible to all users located in region 1, 5 and 8, but also to n selected users not located in region 1, 5 and 8.

the geographical region of a user is stored in the user table (for example region:4)

my question is where to store the project visibility, project regions and selected users and how to show (select) all relevant projects to a user.

i need to show the user a list of all projects

• that are visible to all users
• that are visible to the user because of his region
• that are visible to the user because he is selected to see the project

any help would he highly appreciated, thanks!

[alexsts]

user table - holds info about user including access level for the project
access table - hold data on which part of the project invisible for what access level (by default what is not in this table is visible) or versa versus, your choice.
access assignment table -- decode of access levels for reference mostly.
If you have regional dependency than create one more table regonal_access which will hold data about regions and FK for users