Lots of Bits

Discussion in 'ASP .Net' started by Jack, Jan 19, 2005.

  1. Jack

    Jack Guest

    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
     
    Jack, Jan 19, 2005
    #1
    1. Advertising

  2. Jack

    Jeremy S. Guest

    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" <> wrote in message
    news:...
    > 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., Jan 19, 2005
    #2
    1. Advertising

  3. Jack

    Jack Burton Guest

    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



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Jack Burton, Jan 19, 2005
    #3
  4. Jack

    Jeremy S. Guest

    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!



    "Jack Burton" <> wrote in message
    news:%23aCy8vd$...
    >
    > 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
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
     
    Jeremy S., Jan 19, 2005
    #4
  5. Jack

    IPGrunt Guest

    (Jack) confessed in
    news::

    > 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
     
    IPGrunt, Jan 19, 2005
    #5
  6. Jack

    MWells Guest

    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" <> wrote in message
    news:Xns95E2DF900BA4Aawrench4allnuts@130.133.1.4...
    > (Jack) confessed in
    > news::
    >
    > > 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, Jan 19, 2005
    #6
  7. Jack

    Jack Burton Guest

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

    Cheers,
    Jack



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Jack Burton, Jan 19, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. GGG
    Replies:
    10
    Views:
    12,741
    Donar
    Jul 6, 2006
  2. sarmin kho
    Replies:
    2
    Views:
    856
    A. Lloyd Flanagan
    Jun 15, 2004
  3. Miki Tebeka
    Replies:
    1
    Views:
    465
    Marcin 'Qrczak' Kowalczyk
    Jun 14, 2004
  4. brad
    Replies:
    9
    Views:
    401
    Bruno Desthuilliers
    Jun 19, 2008
  5. coolneo
    Replies:
    9
    Views:
    227
    coolneo
    Jan 30, 2007
Loading...

Share This Page