db entries visibility (design and select help)

This site is best viewed in a modern browser with JavaScript enabled.

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