When is it better to store flags as a bitmask rather than using an associative table?

by

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).

More Related Contents:

  1. How to make an algorithm to get all column names from across tables that relations with their primary key in SQL Server and C#?
  2. whats wrong with this code??(c1.CommandText = "insert into stu values='" + textBox1 + "','" + textBox2 + "'";)
  3. Call a stored procedure with parameter in c#
  4. Execute a large SQL script (with GO commands)
  5. Calling stored procedure with return value
  6. Conversion of a datetime2 data type to a datetime data type results out-of-range value
  7. How to directly execute SQL query in C#?
  8. Entity framework very slow to load for first time after every compilation
  9. How do I return multiple result sets with SqlCommand?
  10. Visual Studio: ContextSwitchDeadlock
  11. Query extremely slow in code but fast in SSMS
  12. How to connect to SQL server database from a Windows 10 UWP app
  13. ExecuteNonQuery for SELECT sql statement returning no rows
  14. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  15. SQLException : String or binary data would be truncated
  16. SSIS Task for inconsistent column count import?
  17. How do I perform an insert and return inserted identity with Dapper?
  18. Entity-framework code is slow when using Include() many times
  19. Creating a SQL Server table from a C# datatable
  20. SQL: Update a row and returning a column value with 1 query
  21. Is there an Entity Framework 7 Database-First POCO Generator?
  22. Creating a database programmatically in SQL Server
  23. Invalid column name sql error
  24. Retrieve image from database in asp.net
  25. Entity Framework | Sequence contains more than one matching element
  26. How to pass a null variable to a SQL Stored Procedure from C#.net code
  27. Is there a .NET equivalent to SQL Server’s newsequentialid()
  28. How to Specify Primary Key Name in EF-Code-First
  29. Subset sum problem
  30. Watch for a table new records in sql database

Leave a Comment