Rather than storing a bunch of settings as Bit fields, and COALESCING them or manually checking their value using a BIT MASK will allow you to store them ALL in a single field, much like the STATUS field in sysdatabases. You can use Logical operator AND and OR on them.
First off, define what your values mean in the bit mask, in this example, I'm choosing a sequence of numbers that double each time, that way, the sum of any combination of each value will be unique.
In this example, I'll use cheerleaders vitals as the data, so, first ill define what each value means. These should probably be stored in a table, but for this example I wont need to.
1 = IsFemale
2 = IsBlonde
4 = IsBrunette
8 = IsOVerTwentyOne
16 = IsMale
32 = IsOverThirtyFive
Now, if we were looking for a Female who is Blonde, we're looking for the value 3. If we're searching for a bloke over 35, then that value is 48.
We can store and populate the cheerleaders table like this:
create table tblCheerleader
(
CheerLeaderID int identity(1,1) primary key,
[Name] varchar(50),
Stats varbinary(1000)
)
insert tblCheerLeader ([Name], Stats)
values ('Miss Scarlett', 1+2+8)
insert tblCheerLeader ([Name], Stats)
values ('Chantelle', 1+4+8)
insert tblCheerLeader ([Name], Stats)
values ('Jenna', 1+32)
insert tblCheerLeader ([Name], Stats)
values ('Bob', 32+16+4)
A quick SELECT proves that the data is stored as BINARY despite being passed in as a decimal sum.
CheerleaderID Name Stats
-------------- ------------------ --------
1 Miss Scarlett 0x00000017
2 Chantelle 0x0000000F
3 Jenna 0x0000000A
Now, how to query them, you're looking to AND the value of the attribute with the value stored in the database, the operation will return same value you passed in, if the result is true.
So, if we're looking cheerleaders, and the only attribute we care about is the person needing to be blonde, then this works:
select * from tblCheerLeaders where stats & 1 = 1
if we're looking for multiple attributes, say blonde and over 35
select * from tblCheerLeaders where stats & 34 = 34
You can get more picky, next we're looking for females over 21 or males over 35
select * from tblCheerLeaders where (stats & 9 = 9) OR (stats & 48 = 48)
Thats it!