sql server – Complex rights structure for users/members and keeping it fast

We are working on a database that holds various information about 15.000 members in a complex structure. These members are managed/viewed by about 1.000 and we need a system to handled rights for these users. You are only allowed to view the members you have had connection with in the last 3 months or currently have one.

The problem we are facing is that it’s very slow to find out which members you are allowed to see, because of the various ways you can have a connection.

These members have parents(~2 per member), a location (1 or more of 500), a principal (1 or more of 100) and a hobby (1 or more of 100). All of these are separate tables.

There is a connection for these members in several tables

(fixedconnection) (~500.000 rows)
memberid, locationid, principalid, startdate, enddate, dayofweek

(fixedconnectionhobby) (~10.000.000 rows)
fixedconnectionid, hobbyid, startdate, enddate, dayofweek

(dailyconnection) (~10.000.000 rows)
memberid, locationid, principalid, hobbyid, date

(parents) (~30.000 rows)
member, parent

Then the actual users also have a rights structure to define what they can see. There are companies and for each company you can select zero, one or multiple locations, principals and or hobbies. If nothing is selected, you can see all.

Each company has departments, where you can select a subset of the chosen locations for the company. If nothing is selected, you can see what the company can see. If something is selected, you can only see those.

Each dapartment has users connected and these users login.

These users have various lists they want to see and select one item to view the details, like:

  • Show me all the dailyconnections
  • Show me all the members
  • Show me all the locations
  • Show me all the hobbies

We need a way to determine in SQL which records we are allowed to show. At the moment we use a combination of joins and union all to combine all the various options to get a list of ids for a specific type. For example all the member ids we can view. We can use this in the query to view the member list.

Obviously this will make the queries quite slow, even with proper indexing, because you are constantly juggling around milions of connections, just to show a single member.

We tried to use caching and get a single table with all the members you can see, per user, but since there are constant modifications to the members and their connections, it changes often who you can see.

My question: How would you handle a structure like this, to prevent users from seeing members they shouldnt see, and keeping it fast.