0

I'm trying to come up with a simple way to track a list of enabled services for customers in a 2008 MS SQL Server database.

There are 256 possible enabled services that a customer can subscribe.

For each customer I need to know which services are enabled, and which services are disabled.

I was thinking of using some sort of bitmask in sql server. I was thinking of defining the bitmask as a 32 byte binary column. That would give me 256 0 or 1's to flip on or off in one column. An enabled service would have the bit on or 1, and a disabled service would have the bit off or 0.

I would prefer to have the left most significant bit refer to Service 1, and the right most bit refer to Service 256. In other words the bits are read from left to right.

Looking for an easy way to turn on any bit in the bitmask. I've been playing with the bitwise or '|' operator.

Ideally I would write a UDF that would make it easy to turn on any bit using a decimal number to signify which bit to turn on.

For example if I want to turn on bit 110, I am looking to do something like: Bitmask = Bitmask | 110.

Spent all afternoon trying to come up with something, and searching the web, but haven't been able to pull it all together.

Any help and suggested approaches would be greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jack Allen
  • 103
  • 1
  • 7
  • 1
    What's wrong with a simple Many-to-Many table between customers and services? Or even a table with 256 sparse columns? – Panagiotis Kanavos May 29 '15 at 12:27
  • Until you've identified a specific reason to deviate from a relational model, you should use the relational model. This sounds like a many-to-many join scenario. That being said, SQL Server does support bitwise operations on integers. – Stuart Ainsworth May 29 '15 at 12:30

1 Answers1

0

The best way to use a bitmask is to not use a bitmask.

First, SQL Server already packs BITs into bytes when storing them to disk. So the bitmask work is already done for you, basically. 256 BIT columns will occupy 32 bytes of space on disk.

However, that's still probably not the best approach to this problem. Since each BIT field represents a service, you're still creating a lot more work in the future. Not only are you still breaking 1NF with a repeating field, you're also requiring yourself to modify the table schema whenever a service is added, removed, or altered. Whether you're using a bitmask or series of bit fields won't fix that. (With a bitmask, if service bit 0x4 gets retired, you have to maintain that useless data forever, and reassignment carries the problem of documenting a change that isn't kept in the DB.)

The best way to solve this is to use the standard many-to-many relationship. Put the list of available services in it's own table, and then use a junction table to represent service assignments. Now you can add and remove services easily, maintain integrity with foreign keys on the junction table, and you can even record data about the relationship in the junction table itself.

Yes, it can be a pain to query from the application because you don't get everything in a single row. It's still the best way to store a relationship of the type you're describing in an RDBMS.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66