db entries visibility (design and select help)
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!