Question regarding database internal structure

[CHeighlund]

I'm working with a form that has a large number of boolean values that need to be stored to the database. These values tend to come in sets - a single form entry might contain checkboxes for two to ten different options, all of which need to be recorded whether the form value is yes or no.

Would it be more efficient (db processing time and/or storage space) to make each individual boolean value a db field, or would it be more appropriate to make a single char(X) field for the set (where X is the number of boolean items in the set) that gets marked as some binary pattern (ex: 5 items, first and third ticked, yields pattern 10100 in a char(5) field)?

 

[stefanwagner]

I don't know which would be faster.
Theoretically you could store 8 booleans in a byte but perhaps for performance reasons each value is stored as 32bit value or 64bit on a 64bit machine internally.

I would use booleans as long as no strong suggestion is pointing in another direction.

Did you think of how to query the database with combined values?

... WHERE b4 IS TRUE or b5 IS TRUE;

Regular expressions?

... WHERE b1_8 LIKE '???1????' OR b1_8 LIKE '????1???';

How to update '??0???' to '??1????'?

Another approach is to add integers:
boolean value
1.......1
2.......2
3.......4
4.......8

for b1 AND b3 AND b4=true add 1, 2, 8 = 11

This way you save a maximum of space and pay with CPU and usability.

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[gwinn7]

What about storing these boolean values in a single field, but instead of a string, use an array of booleans? PostgreSQL supports storing an array in a single column.

Something like...

create table mytable
(
id int4 not null,
myvalue boolean[8],
myothervalue boolean[],
);

Gary
gwinn7