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

问题描述

我正在开发一个应用程序，其中用户具有使用不同功能(例如阅读、创建、下载、打印、批准等)的不同权限.权限列表预计不会经常更改.关于如何将这些权限存储在数据库中，我有几个选项.

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

选项 1

使用关联表.

<前>用户----用户 ID (PK)名称部

权限----PermissionId (PK)姓名

用户权限----用户 ID (FK)PermissionId (FK)

选项 2

为每个用户存储一个位掩码.

用户----用户 ID (PK)名称部权限

[标志]枚举权限{读取 = 1,创建 = 2,下载 = 4,打印 = 8,批准 = 16}

解决方案

好问题！

首先，让我们对更好"做一些假设.

我假设您不太关心磁盘空间 - 从空间的角度来看，位掩码是有效的，但我不确定如果您使用 SQL 服务器，这是否重要.

我假设您确实关心速度.使用计算时位掩码可以非常快 - 但在查询位掩码时您将无法使用索引.这应该无关紧要，但是如果您想知道哪些用户具有创建访问权限，您的查询将类似于

select * from user where permsission &创建 = 真

(今天无法访问 SQL Server，在路上).由于数学运算，该查询将无法使用索引 - 因此，如果您有大量用户，这将非常痛苦.

我假设您关心可维护性.从可维护性的角度来看，位掩码不如存储显式权限的基础问题域那样具有表现力.您几乎肯定必须跨多个组​​件(包括数据库)同步位掩码标志的值.并非不可能，但背部疼痛.

因此，除非有另一种评估更好"的方法，否则我会说位掩码路由不如将权限存储在规范化的数据库结构中.我不同意它会更慢，因为你必须进行连接"——除非你有一个完全功能失调的数据库，否则你将无法衡量这一点(而没有活动索引的好处的查询可能会变得明显即使有几千条记录也会变慢).

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屋！