NYCPHP Meetup

[Kristina Anderson]

> Kristina,
> 
> While we're on this subject, I thought I would ask another pending 
> question I have related to this.
> 
> I need to store a bunch (and by a bunch, I mean about 30-40) binary 
> true/false values in my database.
> 
> In the past, I would do something like this:
> 
> CREATE TABLE........... (
>  				is_active tinyint(1) default 1,
>  				is_friend tinyint(1) default 1,
>  				is_something tinyint(1) default 1,
>  				is_something_else tinyint(1) default 1,
>  				......
>  			);
> 
> But that table would really get large if I had 30-40 extra columns in 
it. 
> The other option was that I was considering a bit string:
> 
> 
> CREATE TABLE......... (
>  				permissions mediumint(11) default 0
> );
> 
> INSERT INTO permissions (permissions) values (7);
> 
> Since there are 3 bits that make up the integer "8" (or 0 - 7):
> 
> 1,2,4
> 
> This would mean that I could store 3 combinations of permissions, but 
it 
> would be an integer.
> 
> This could get even more complex if I had 30-40, as I could do a 
really 
> large hex string, or just store the actual bit string:
> 
> 
> aef214  <--- one value
> 11000110111011 <-- or something like that
> 
> 
> Thoughts?
> 
> -Matt
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> 
> http://www.nyphp.org/show_participation.php
> 
> 

>>I need to store a bunch (and by a bunch, I mean about 30-40) binary 
>>true/false values in my database.

>>In the past, I would do something like this:

>>CREATE TABLE........... (
>>                   is_active tinyint(1) default 1,
>>                   is_friend tinyint(1) default 1,
>>                   is_something tinyint(1) default 1,
>>                   is_something_else tinyint(1) default 1,
>>                   ......
>>              );

>>But that table would really get large if I had 30-40 extra columns in 
it. 

Hi Matt, 
Thanks for your question.  AFAIK, the above is still the way it's done 
most of the time.  From what you write below, it seems that you are 
trying to optimize the potential size of the database by being very 
parsimonious about the size and quantity of the fields.

>>The other option was that I was considering a bit string:


>>CREATE TABLE......... (
>>                   permissions mediumint(11) default 0
>>);

>>INSERT INTO permissions (permissions) values (7);

>>Since there are 3 bits that make up the integer "8" (or 0 - 7):

>>1,2,4

>>This would mean that I could store 3 combinations of permissions, but 
it 
would be an integer.

>>This could get even more complex if I had 30-40, as I could do a 
really 
large hex string, or just store the actual bit string:

>>aef214  <--- one value
>>11000110111011 <-- or something like that

Here you are facing an issue between readability/ease of retrieval and 
space needed to store.  I think that the idea of using a string of 0's 
and 1's in one field is interesting & useful, and is readable enough so 
that if something is awry you'd be able to find it fast.  Some string-
handling functions to pull out individual values & update the field as 
needed, and you're rolling.

I'd suspect that in a very large database, by using one field of an 
appropriate size to store a string of 40 booleans, rather than 40 
tinyint fields, you could save some really appreciable room.   

Kristina