Check boxes

himik has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks. I have some difficulties so i call on you.

So i have at about 100 checkboxes. And every one is different extra for a car ( ABS,ASR,ESP and the list goes on .... until 64 for now, but it will be bigger then 100 ).

So what have i done for now. Every extra abs has decimal number like abs is 1, ASR is 2, ESP is 3 .... This numbers represent the n-th bit of the number of all extras.

explanation if ABS is checked i put 1 only in the field extri on the data base if ABS, ASP are checked i put 3 on my data base. if ABS, ASP, ESP are checked i put 7 on my data base.

so 7 is 0b111? right? And this is my flags for all the extras.

if i have 3 on my field extri then i know (0b11) the first bit and the second one is up then i know that this extras are ABS ASP

Here is the problem

My field extras is big int and it is 64 bits long .... that means i have only 64 extras. so i start to look for solution and i'm thinking for bit var.

but ..... is this the best way to make that?

could you tell me some other solution that is better for the DB, because with this way my searching is so easy just put where clause with

select * from table where
(table.extri & b'$extribin_perl')=b'$extribiin_perl'
[download]

i do not use any joins

Thank you

Comment on Check boxes Download Code

Replies are listed 'Best First'.
Re: Check boxes by BrowserUk (Patriarch) on May 28, 2012 at 17:35 UTC
If you use PostGreSQL, it has bit string types which can be any number of bits and support direct boolean manipulations. They are directly analogous to Perl's bitstrings which is very useful. With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday' Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. The start of some sanity?	[reply]
Re: Check boxes by mbethke (Hermit) on May 28, 2012 at 17:18 UTC
The easiest way in terms of SQL would be to have an extra boolean column for each feature. The way you're doing it is certainly the most compact but depending on how many rows you have it can get very slow because the DB has to do a full table scan for each query. And then as you have noticed you run into problems when you need more bits than the largest integer type has. As far as I know there's no nice workaround for >64 features, only ugly ones like splitting them up in "extri" and "extri2" or something. Of course BLOBs could be as big as you like but the boolean operators don't work on them. If your database server is on the same machine as the application, you could just select everything and do the filtering in Perl, I don't think it would be much slower than doing the same in SQL. Though that goes against pretty much the entire idea of a relational database ... In the end I'd say you'd be best off to use individual columns. With TINYINTs that would be some 100 bytes per row on MySQL and probably less on Postgres, not that much for all the advantages it brings.	[reply]
Re^2: Check boxes by bitingduck (Deacon) on May 29, 2012 at 05:50 UTC
Another option would be to add two new tables and go ahead and do a lookup with a join: One table that lists all the options and assigns a key to each one A second table that has two columns-- one is just Car_ID and the other is feature_ID. This is the join table. If car#23 has features #76, 53, and 99 then it would get three rows in the join table, each of which would be the car ID and one of the numbers. For the OP this might be overkill, since there aren't likely so many cars that the table will get big, even if it has a lot of columns. It might be slightly slower on the db side, because you have to do a join, but it's probably not significant, again because the tables are probably small. Just another way to do it.	[reply]
Re^3: Check boxes by mbethke (Hermit) on May 29, 2012 at 06:15 UTC
The SQL to ask for several features ("which car has ABS and ESR and teledildonics") would get pretty horrible though as you'd have to join in the features table once for each feature. Possible but very very slow and only halfway readable with DBIx::Class and chained ResultSets.	[reply]
Re: <strike>Check boxes</strike> SQL Boolean arrays by NetWallah (Canon) on May 28, 2012 at 17:27 UTC
Depending on what SQL you are using, you may be able to define an array of SQL booleans, avoiding lentgh limitations. `<binary string type> ::= BINARY [ <left paren> <length> <right paren> +] \| { BINARY VARYING \| VARBINARY } <left paren> <length> <right paren +> \| LONGVARBINARY [ <left paren> <length> <right paren> ] \| <binary l +arge object string type>` [download] If BINARY is used without specifying the length, the length defaults to 1. I hope life isn't a big joke, because I don't get it. -SNL	[reply] [d/l]
Re: Check boxes by flexvault (Monsignor) on May 28, 2012 at 17:53 UTC
himik, Disclaimer: Have used SQL, but not in a long time. So I'm more interested in the Perl benefits to the solution. Why not define a string field of 16 characters(128 bits) or whatever and use Perl to encode/decode the 'checkbox' string. if "\0" cannot be supported by you database then this wouldn't work. You can test this by sending a string of '0..255' to the data base and then see if it returns all characters exactly. (Untested). `my $str = ""; for ( 0..255 ) { $str .= chr($_); }` [download] Perl's pack/unpack may be very valuable for this. Have you ruled out a 128 character string with "Y" or "N" as a possibility? Disk space is pretty cheap today! Good Luck! "Well done is better than well said." - Benjamin Franklin	[reply] [d/l]