Page 1 of 1

Relational Bit Fields

Posted: Sat May 06, 2006 5:00 pm
by Todd_Z
Okay, So heres the setup:

a table with `groups` - relatively small amount, lets say a maximum of 16 for the hell of it.

a table of `users` with the field `groups`

the groups table has an id -> auto_incrementing

the users table has a field `groups` which is a bitwise interpretation of the groups.

UPDATE users SET groups = pow(2,X) + pow(2,Y) + pow(2,Z) for example....

Anyways, I want to make this as efficient as possible.

Right now the id field for the groups is int(1) unsigned
the groups field in the users table is int(4) unsigned.

I have no idea what the maximum amount of groups i can have, which field is going to limit expansion, and whether using int instead of a bit field is a bad idea for optimization purposes.

Posted: Sat May 06, 2006 5:12 pm
by timvw
What are you trying to optimize exactly? And what was the reason for the decision to do it?

How about another table user_groups? It gives you the advantage of referrential integrity, ...

Posted: Sat May 06, 2006 5:16 pm
by Todd_Z
optimize the overall size that each row of the database takes up...

I dont really have to worry about the read / write speed.... so really its just the physical size of the database to deal with.

I was using a relational table, but I'd rather use bitwise operators - for whatever reason...


Basically the answer to your question is cause I've never used bitwise relations, and I want to try it out

Posted: Sat May 06, 2006 9:55 pm
by feyd
Use a SET field type. They are bit fields in storage with only the named components being in the table description. Comes out far more human readable although you can query for the numeric representation too. :)

Posted: Sat May 06, 2006 10:02 pm
by Todd_Z
That looks good - but the problem is that a set is static - determined at table creation. The group list is dynamic.

Posted: Sat May 06, 2006 10:08 pm
by feyd
You can alter the field after creation. Or you can set up a one-to-many relationship to another table. I find bitfields and likewise sets (and enums) fairly limiting. Sure they work to a point, and sometimes you'll only have so many.. but my bit sets rarely fit in a single numeric size.