什么时候更好地将旗标存储为位掩码，而不是使用关联表？ [英] When is it better to store flags as a bitmask rather than using an associative table?

问题描述

我正在使用一个应用程序，用户具有不同的权限以使用不同的功能（例如，读取，创建，下载，打印，批准等）。权限列表预计不会频繁更改。我有几个选择如何将这些权限存储在数据库中。

选项2在什么情况下会更好？

选项1

使用关联表。

 
用户
 ---- 
 UserId（PK）
名称
部门
 

权限
 ---- 
 PermissionId（PK）
名称

 User_Permission 
  - --- 
 UserId（FK）
 PermissionId（FK）

选项2

存储每个用户的位掩码。

用户
 ---- 
 UserId（PK）
名称
部门
权限

  [Flags] 
枚举权限{
读= 1，
创建= 2，
下载= 4，
打印= 8，
批准= 16 
} 
  

解决方案

Splendid question！

我们假设你不太在意磁盘空间 - 一个位掩码从空间的角度来看是有效的，但是如果您使用SQL Server，我不太确定这个问题。

我假设你关心速度。使用计算时，位掩码可能非常快 - 但是在查询位掩码时无法使用索引。这不应该是重要的，但是如果你想知道哪些用户创建了访问权限，你的查询将会像

  select * from user where permsission& CREATE = TRUE 
  

（今天没有访问SQL Server的路上）。这个查询将无法使用索引，因为数学运算 - 所以如果你有大量的用户，这将是相当痛苦的。

我是假设你关心可维护性。从可维护性的角度来看，位掩码与存储显式权限的底层问题域不如表达一样。您几乎肯定必须同步多个组件（包括数据库）中位掩码标记的值。不是不可能，而是背部疼痛。

所以，除非有另一种方法来评估更好，否则我会说bitmask路由不如在一个规范化的数据库结构中存储权限。我不同意，因为你必须加入一点，因为更慢 - 除非你有一个完全功能不全的数据库，否则你将无法测量这一点（而没有活跃索引的好处的查询可以变得显着）慢甚至几千记录）。

I’m working on an application where users have different permissions to use different features (e.g. Read, Create, Download, Print, Approve, etc.). The list of permissions isn’t expected to change often. I have a couple of options of how to store these permissions in the database.

In what cases would Option 2 be better?

Option 1

Use an associative table.

User
----
UserId (PK)
Name
Department

Permission
----
PermissionId (PK)
Name

User_Permission
----
UserId (FK)
PermissionId (FK)

Option 2

Store a bitmask for each user.

User
----
UserId (PK)
Name
Department
Permissions

[Flags]
enum Permissions {
    Read = 1,
    Create = 2,
    Download = 4,
    Print = 8,
    Approve = 16
}

解决方案

Splendid question!

Firstly, let's make some assumptions about "better".

I'm assuming you don't much care about disk space - a bitmask is efficient from a space point of view, but I'm not sure that matters much if you're using SQL server.

I'm assuming you do care about speed. A bitmask can be very fast when using calculations - but you won't be able to use an index when querying the bitmask. This shouldn't matter all that much, but if you want to know which users have create access, your query would be something like

select * from user where permsission & CREATE = TRUE

(haven't got access to SQL Server today, on the road). That query would not be able to use an index because of the mathematical operation - so if you have a huge number of users, this would be quite painful.

I'm assuming you care about maintainability. From a maintainability point of view, the bitmask is not as expressive as the underlying problem domain as storing explicit permissions. You'd almost certainly have to synchronize the value of the bitmask flags across multiple components - including the database. Not impossible, but pain in the backside.

So, unless there's another way of assessing "better", I'd say the bitmask route is not as good as storing the permissions in a normalized database structure. I don't agree that it would be "slower because you have to do a join" - unless you have a totally dysfunctional database, you won't be able to measure this (whereas querying without the benefit of an active index can become noticably slower with even a few thousand records).

这篇关于什么时候更好地将旗标存储为位掩码，而不是使用关联表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！