Re: Check boxes

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.

Comment on Re: Check boxes

Replies are listed 'Best First'.
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]