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
"IPGrunt" <me@privacy.net> wrote in message
news:Xn*****************************@130.133.1.4.. .
ja****@humlog.com (Jack) confessed in
news:6e*************************@posting.google.co m:
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
