Relational Bit Fields

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Relational Bit Fields

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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, ...
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. :)
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

That looks good - but the problem is that a set is static - determined at table creation. The group list is dynamic.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply