database design question

I want to create a system with php and probably mysql, but since my question is not mysql specific, I'll post it here.

The system to be created has items, which should be viewable for certain groups of users. A user may be part of several groups, and an item may be intended for several groups.

How would you implement this group-matching in the database?

My idea was to make a field that is 256 bit long (there will never be more than 256 groups, i hope).

The user and the item have this field, and if you AND the field together and the result is not all 0's, the user should get the item in his item list.

But it this 'the right way' to do it? The 256 group limit is not perfect, and the I don't know if I can AND the fields in the sql query.
Items can be everything, from news articles to files to you name it.

you need a many-to-many relationship between users and groups

then you need a many-to-many relationship between items and groups

altogether that's 5 tables

USER
userid PK
userdata

USERGROUP
userid FK
groupid FK

GROUP
groupid PK
groupdata

ITEMGROUP
itemid FK
groupid FK

ITEM
itemid PK
itemdata


rudy
http://r937.com/

5 tables, nice.

And how do I compose a query for listing all items for one particular user?

Is this ok ?

SELECT item.* from user,usergroup,group,itemgroup,item where user.userid='1' and user.userid=usergroup.userid and group.groupid=usergroup.groupid and group.groupid=itemgroup.groupid and item.itemid=itemgroup.itemid;

yes, that's it! nice job on the joins

tip 1: always itemize (no pun intended) the columns you want, instead of using the asterisk

tip 2: you may want to consider using DISTINCT in this example, if a given user belongs to more than one group, and those groups are related to items in common

rudy

thnx.

I know about the *, but i didn't think of the distinct.

thanks again.