flags in mysql

Discussion in 'OT Technology' started by crotchfruit, Jul 26, 2003.

  1. crotchfruit

    crotchfruit Guest

    so let's say that an item can have 20 flags - a flag being a simple boolean true/false.

    i want each item in a mysql db to have these 20 flags defined. in this case i would just add an 'int' column to the table and use the bits of the int to be the flag values. this works great.

    now, here is the question: let's say i want 100 flags. i can't use a single int anymore, since it doesn't have 100 bits. i could always make X int columns to span the 100 bits, but this doesn't seem elegant to me. is there a better way to imlpement flags in mysql that ints?

    i guess i could make a string that was like "01010100100101..", but one reason why ints are good is that i can use bit operations when selecting rows i.e. "select blah where (flagint & 4 > 0)". is there anything like that for strings, like "select blah where char(flagstring, 4) == '1'"?
     
  2. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
  3. crotchfruit

    crotchfruit Guest

    while decimal has good range, it doesn't appear to provide any flag optimizations...

    i am perfectly happy using strings of '010101001..', but i can't find the sql command that compares specific chars of a string. basically, if each row had a 100 character string of either '1' or '0' i want to do something like this:

    select from my_table where [third char of my_string_flag = '1'].

    i can't find the "xth char of string" function.. does it exist?
     
  4. crotchfruit

    crotchfruit Guest

    duh, i guess it's substring. i hope it's not slow :(
     
  5. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Yeah, substring() will work.

    I lack your logic and reasoning for the design approach you're taking, but if you have a large string of flags to test with a lot of rows, substring() is going to put a dent in the performance versus using bit operations with two bigint fields (which would give you 128 bits to play with).

    The other option would be to bust out a flag table and a cross reference table. Maintenance would be a little easier when it comes time to add and subtract flags. You'd still be taking a performance hit with the joins though (although chances are you can use indexes which would help greatly).
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You mean a table that has one field refering to the original table, a second field that refers to the flag (could be text or numeric), and a third field to turn it off and on? That would be my suggestion as well then. This table can be indexed so that the first two columns are primary indexed so that you can't have a repeat flag per "original" record. The other advantage is that you don't have to have every flag for every record - you can choose to only list the ones that are "on" - if a corresponding flag doesn't exist you can assume it's off.

    Either that or a shitload of boolean fields :rofl:
     
  7. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    hmmm, i wouldn't use strings, but it may do what you're looking to accomplish. Is this like a "user preference" table or something where they have multiple selection options? in other words, you need the capability to use any combination of those flags? What version of mySQL are you using. I know the newest (might be beta) has support for User Defined Functions, and that would really help your string idea.
     
  8. crotchfruit

    crotchfruit Guest

    ya, that's a good analogy. let's say i have a table called "users" - to store user data. part of the user data is a huge table of preferences. let's say my site gives the user the ability to toggle 200 different preferences to on or off.

    the obvious solution is to use, say, 4 BIGINTs - with each bit of an int representing a preference. the problem here is that a) it's not really that flexible, since if i then go to 300 preferences i need to add another column, change some code around, etc. and b) it's not really elegant.. theoretically i would like to have one column contain all 200 boolean flags (i.e. a 200 character string of 010101...) which simplifies select statements and improves expansion possiblity.

    the end result is that i want to be able to select from the table all users with a specific 'preference' enabled.. for example (pseudo):

    select * from users where [preference 198 = 1] AND [preference 34 = 1].

    i can do this with BIGINTS + bitwise ops, but it means doing extra work to know which column a preference is in. i can do this with a X length string, but then i have the slowness of substring.

    i guess using the X number of BIGINT columns isn't that bad, i just feel it's not as clean as it "could be". i was just hoping there was a super-awesome solution to this that i hadn't heard of.
     
  9. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    I'd be aiming for 2 additional tables...

    Table 1: Flag definitions:

    Name: flags
    myflagID - bigint - unsigned - auto increment - index
    myflagName - varchar - 50 (or whatever size you need it)
    myflagDescription - text (option - you can describe the flag here)

    Table 2: The cross reference: xreftable

    Name: xrefTable
    myflagID - bigint - unsigned - index
    myUserID - bigint - unsigned - index

    Here you can then query all users that have flag ID 198 and 34:

    SQL username FROM user INNER JOIN xrefTable ON xrefTable.myUserID = user.myUserID WHERE myflagID = 198 AND myflagID = 34

    Now you can have a boat load of flags. You can add flags whenever you want without having to update your code. The indexes will provide very nice performance (much much better then substring()).
     
  10. crotchfruit

    crotchfruit Guest

    Astro: i didn't understand what you were talking about before, but now that you laid out how to do it, i think your idea rocks :bowdown:

    it's especially good since, for my purposes, each "user" was only going to have a few of the 200+ flags set to '1' at any given time - which will help minimize the size of the cross reference table.

    :cool: thank you :hug: :bigthumb:
     
  11. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Depending on your requirements, you may find you need to use "LEFT OUTER JOIN" instead of "INNER JOIN". I'll admit every now and then I botch this up with my code and I'll sit there scratching my head trying to figure out why I'm not getting any records returned.

    If you're not sure, let me know and I can probably give you a quick crash course on when to use which...
     
  12. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Don't worry about the size of your cross reference table. Here's why:

    Both fields will probably be bigint (or some flavor of). If you tell MySQL to index both fields, MySQL performs awesome. Even if you manage a ton of records (even 1 million+), it will be fine. With indexing and being numeric, MySQL can still quickly pull out what you need (well, really any database engine can).
     
  13. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Apparently I didn't explain Astro's way of doing it well either :rofl:
     

Share This Page