is it better to structure an SQL table to have a match, or return no result

Question

I've got an interesting design question. I'm designing the security side of our project, to allow us to have different versions of the program for different costs and also to allow Manager-type users to grant or deny access to parts of the program to other users. Its going to web-based and hosted on our servers.

I'm using a simple Allow or Deny option for each 'Resource' or screen.

We're going to have a large number of resources, and the user will be able to set up many different groups to put users in to control access. Each user can only belong to a single group.

I've got two approaches to this in mind, and was curious which would be better for the SQL server in terms of performance.

Option A The presence of an entry in the access table means access is allowed. This will not need a column in the database to store information. If no results are returned, then access is denied.

I think this will mean a smaller table, but would queries search the whole table to determine there is no match?

Option B A bit column is included in the database that controls the Allow/Deny. This will mean there is always a result to be found, and makes for a larger table.

Thoughts?

Answer 1

I would vote for Option B. If you go with Option A and the assumption that if a user exists, they can get in, then you'll eventually run into the problem that you'll want to deny access to a user, without removing the user record.

There will be lots of cases where you'll want to lock a user out, but won't want to completely destroy their account. One such instance (not necessarily linked to your use case), is when you fail to pay, and they cut off your account until you start paying again. They don't want to delete the record, because they still want to enable it when you pay up again, instead of recreating the account from scratch, and losing all user history.

Answer 2

B. It allows for much better checks whether the data is complete (for example, when you add an allowable/deniable feature).

Also, table size should only be a consideration for tables that you know will contain many records (as in, 100,000+). You even taking the time to type the table size consideration into this question already cost more than the extra hard drive space it would take.

Answer 3

If it's only going to be Allow/Deny, then a simple linking table between Users and Resources would work fine. If there is an entry keyed to the User-Resource in the linking table, allow access.

UserResources
-------------
UserId FK->Users
ResourceId FK->Resources

and the sql would be something like

if exists (select 1 from UserResources 
where UserId = @uid and ResourceId=@rid)
set @allow=1;

With a clustered index on (UserId and ResourceId), the query would be blindingly fast even with millions of records.

Answer 4

Approach A, but I would also include a explicit deny in addition to you implicit deney. I would make some use cases to be sure your end logic works but here are some examples.

User1 is in group1 and group2.
User2 is in group1
User3 is in group2

Folder1 allows group1 and deny group2.
User1 is denied.
User2 is allowed.
User3 is denied.

I believe your approach users1 would be allowed.