CODEWITHSUNDEEP
sql-serverdatabase-designforeign-keys
Marek Kwiendacz
Marek Kwiendacz

Reputation: 9864

Optional foreign keys - is that good solution?

I have DB with few permission tables in form: 

UserId, Object Id, lot of bit fields

I've added users groups to my DB and I need to update permissions to work with users and users groups. I think about 2 approaches.

  1. Create copy of each permission table and have 2 tables for each object permission (user and group permission) - in each table I'll have one foreign key to 'permission owner' tables (in one table - to users and in second table to user groups):

    UserId NOT NULL, Object Id NOT NULL, lot of bit fields  
GroupId NOT NULL, Object Id NOT NULL, lot of bit fields

  2. Add one field (GroupId) to each permission table and use one of fields (UserId or GroupId) to identify if it is permission for group or user. So I'll have table with 2 foreign keys - to users and user groups, but for each record only one of those FK will be used - other will be null. Table could looks like this: 

    UserId NULL, GroupId NULL, ObjectId NOT NULL, lot of bit fields

What is the best solution in your opinion? What are pros and cons of both? Are there other, better solution?
EDIT: I need to know what to do with foreign keys to users and groups, not with bit fields.

Upvotes: 1

Views: 419

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

If the types of permissions that both sets can be granted are always the same, I'd probably keep it in the same table. But make sure you add a check constraint:

UserId int NULL,
GroupId int NULL,
constraint CK_CorrectFKs CHECK (
   (UserId is null and GroupId is not null) or
   (UserId is not null and GroupId is null)
),
ObjectId int NOT NULL, lot of bit fields

Or, have you considered modelling groups as users - either just modifying the Users table to accept groups directly, or having the Groups table (with "group only" columns) referencing the Users table? It may be a simpler route to go down (depending on which other parts of your database need to only work with users, for example). Then all your permission checks can just be "Here's a list of IDs (one of which is the user, the other are groups), please work out the aggregate permissions for this user".

Upvotes: 2

Jeremy Gray
Jeremy Gray

Reputation: 1418

I would do a third option, which is change your bit fields to tinyint (anything in int family or varbinary would work as well but you probably only need 2-3 flags) and use bitwise operators (no conversion will be necessary) to check the security level. This would not add columns or tables.

I usually do this when there are several combinations of access control necessary for the same resource. For instance I would have an int column called calendar_permissions and assign the following values from least to most significant bits view (1), add (2), edit (4), delete (8). So if I wanted to check permissions on the delete I would do an "intvalue AND 8 = 1" condition. (if the user had all permissions the value would read 15 = 8 + 4 + 2 + 1)

In your case a 1 would be user permission, a 2 would be group permission, and optionally 3 would be a user and group permission. You can expose this through a view if you are not comfortable with your application doing this kind of bitwise arithmetic.

Because you are defining 2 as a group permission your calls to check for the "user" permission should be back compatible because the integer value 1 casts to true.

Optionally you can put a check constraint on these fields to limit the values that make sense to your application.

PRO: No additional columns/tables, back compatible with current system.

CON: Not as human readable, some people have problems with bitwise operations. You may have to create a view to get anyone to actually use it.

Upvotes: 0

Related Questions