Lots of Bits

[Jack]

Hi there, I'm not sure if this the appropriate group so apologies if
it lies outside the boundary.

Senario: I have a customer table with contains a bunch of different
bit values that represent true/false values pertaining to the
customer. I decided for the purpose of clarity I would move these
values into another table. This way I would keep the customer details
(name, age, etc) separate from these values.

Question: Is this a good idea? Or chould i somehow tally up all these
bit values and store it in one field in the customer table?

The application is a website and it is built using ASP.NET (VB.NET)

Cheers,
Jack

 

[Jeremy S.]

Without knowing more about the tables in your database and how they are
related and which "real-world" entities are represented by your tables....
I'd think that you are creating more work for yourself by splitting out a
table simply to hold 1:1 data (you now may have to join the tables in order
to get the bits for a given customer). You do not explain how separating the
bits out into another table increases clarity. I just don't see it (granted,
I have very little to go on).
Regarding tallying up all the bit values - no way - don't do it; that's
creating a lot more work, violates the First Normal Form (of relational
design; "no multi-part values"), and will definitely create a lot more work
for you (e.g., what happens when you need to change one of the bit values?).
Plus, what would the tally actually mean? Answer: it would mean nothing: SUM
1 + 0 + 1 + 1 = 3. Also, SUM 0 + 1 + 1 +1 = 3. So the value of 3 is
meaningless (tells you absolutely nothing beyond the fact that 3 of 4 bits
had a value of 1; unless that is the only fact you are interested in, the
value is meaningless).

You really need to tell us a lot more about the meaning and use of your
data. Plus, the SQL Server/Programming group frequently gives feedback on
this sort of issue (it really has nothing to do with ASP.NET).

Good Luck!

 

[Jack Burton]

Yes sorry I was a bit vague.

These values are used for filtering purposes e.g. searching for
customers who best matches criteria set out in a marketing campaign i.e.
match customer filter values with campaign filter values.

Hope that make sense.

When I say 'clarity' I just mean the customer table was becoming massive
so I thought I'd move the filter values into a another table (yes it
requires another join - i may change it back)


I definitely used the wrong word in 'tally' - sorry. I mean representing
all bit values as one binary value. This value can then be stored in the
database (as a decimal or hex). If one value is just say changed then a
new binary is formed and hence a new value in the database.

Does this make any sense?

This is just a 'throw out there' question as i would be interested in
opinions.

Cheers,
Jack

 

[Jeremy S.]

Okay, your "tally" makes more sense. IMHO, you're getting into some
interesting trade-offs with this "binary value" stored in the db and it
comes down to what you are comfortable living with (like many design
considerations). If the number of binary columns is large and you are
searching on them, then you should also index them. This has some
maintenance and performance and complexity implications. Compared with one
integer column that holds some unique value, then the query is simplified
and performance is perhaps increased for the database. Of course a database
purist might frown upon it for more theoretical purposes as well as the many
practical purposes related to maintaining the unique int value that has your
application-specific meaning. I suspect that if you posted this in the
microsoft.public.sqlserver.programming group that you'd get some intersting
perspective and rationalle beyond what I pointed out (which you probably
were aware of before I pointed it out). As for my opinion; I'd most
definitely lean toward keeping the bits in their own columns... it just
gives you so much more flexibility in extracting information. You can always
create and programmatically maintain a separate denormalized table that
could have your special "tally" column and then query that table for your
current searching/reporting purposes. This gives you the best of both worlds
and is possibly the easiest solution to modify in the future because you
always have the original bits to go back to while your queries can be
simpler and perform fewer (perhaps zero) joins and therefore run faster
because they are hitting a single denormalized table. You get the idea.

Good Luck!

 

[IPGrunt]

(e-mail address removed) (Jack) confessed in

Hi there, I'm not sure if this the appropriate group so apologies if
it lies outside the boundary.

Senario: I have a customer table with contains a bunch of different
bit values that represent true/false values pertaining to the
customer. I decided for the purpose of clarity I would move these
values into another table. This way I would keep the customer details
(name, age, etc) separate from these values.

Question: Is this a good idea? Or chould i somehow tally up all these
bit values and store it in one field in the customer table?

The application is a website and it is built using ASP.NET (VB.NET)

Cheers,
Jack

What purpose would it serve?

One usually creates a new table to normalize a database, that is, to prevent
duplication of data. It's a time against space tradeoff, because you have
added a level of indirection to your data and must now use a map to manage
the pointers, and write the code (via joins) that use that map to get at the
data.

Perhaps if users had common profiles (bit settings) you might try it, but
this sound like more trouble than it is worth. Basically, this is one number
per user, right?

-- ipgrunt

 

[MWells]

It's all about tradeoffs.

Scenario #1 (current): use a bit-encoded field to store boolean(?)
attributes
Scenario #2 (proposed): store these attributes in a joined table, one row
per "true" attribute.
Scenario #3: break the attributes into discrete bit columns. One column
per attribute.

Pros+/Cons- of #1:
+ No joins
- Have to do ANDs and XORs to find matches. Does your db support these
constructs?
- Have to do two queries to apply a single filter; one to specifically
match on "true" attributes, the second to specifically match on "false"
attributes. You have to intersect the results of these two queries to get
your final list. If you only care about "true" matches this doesn't apply.
- Less efficient to manage
- More difficult to understand
+ Might be faster. Maybe.
- Limited number of attributes, depending on the bitfield size.
+/- Moderately expandable; but requires some code to interpret the meaning
of the bit fields. Eliminating a bit field requires an update to the entire
table and your code as well...

Pros+/Cons- of #2:
- Requires a join per attribute test. If you're testing for the
existance/non-existance of three attributes, you need three joins to your
attribute table.
+ Much easier to understand
+ More efficient to manage
- Might be slower. Maybe.
+ Unlimited number of attributes.
+ Ability to link other special data to specific attribute types if you
like; e.g. if you were doing real-estate, you could say "yes" it has
parking, and then link to parking details stored elsewhere.
+ Hugely expandable. Add attributes. Delete attributes. Easy as pie.

Pros+/Cons- of #3:
+ Much easier to understand
+ More efficient to manage
+ No joins
+ Fastest of the 3. No binary math, no joins.
+ Expandable, but limited by the number of fields your table can have.
Adding/Deleting attributes requires a table structure change, but no data
migration.


I'd choose between #2 and #3 personally, depending on the amount of
expansion I need. If I have just a handful of attributes and they change
rarely I'd go with #3. If attributes are a fairly liquid concept, I'd go
with #2.

/// M

 

[Jack Burton]

Thank you for all your replies. It have given me a much clearer idea on
handling this type of senario.

Cheers,
Jack