Help me with the database structure

[retri]

Hey all,

i just started playing with php & mysql, been looking around to see any examples or former questions but couldnt find one.

i'm trying to produce a discussion board with individual permissions to users.

 

lets say i have 50 boards and 100 members in the site. (the numbers are not really important)

there will be usergroups, and each usergroup will have access to certain boards, while some boards will be shared by all usergroups.

There will be 3 kinds of permissions to boards, read, write, moderate.

but what i want here is to be able to give a single user e.g. individual access to a board that its usergroup does not have access. and i'd like to be able to specify the type of the access (read, write, moderate) that user has.

 

the number of boards are not static, i mean i can add boards later or delete them, thus the number of board entries in the database differs.

 

how can i produce such a structure? the usergroup thingy is not difficult but giving individual access to users especially 3 type access, i cant think of a way.

 

i'll be most pleased if you come up with some ideas

thanks 

[DarkWater]

Just thinking of this off the top of my head:

[b]boards:[/b]
id
name
description

[b]users:[/b]
id
username

[b]usergroups:[/b]
id
group_name

[b]board_usergroup_permissions:[/b]
id
board_id
usergroup_id
permission

[b]user_usergroups:[/b]
id
user_id
usergroup_id

[b]board_user_permissions:[/b]
id
board_id
user_id
permission

 

The permission column could be used in a bitmask-y permission system.

[retri]

so are you implying that i add additional rows to the "board_user_permissions" table, for every individual access the same user has to different boards?

 

lol that actually sounds very easy. i've been trying to store all the board permissions to a single column for every user 

must be something that comes with lack of experience 

 

thanks for the fast reply.

[DarkWater]

Yup, that's exactly what it was supposed to mean.  Read up on database normalization if you get the chance.  Also, with the permissions column, sorry if I wasn't clear.  I meant to do things like:

 

define('PERM_USER_READ', 1);
define('PERM_USER_WRITE', 2);
define('PERM_USER_MODERATE', 4);

//if you wanted a user to have access to read and write:
$perm = PERM_USER_READ | PERM_USER_WRITE;

//to check if a user has read + write + moderate permission:
if ($perm & (PERM_USER_READ | PERM_USER_WRITE | PERM_USER_MODERATE)) {

}

Read up on bitwise operators for this.

[retri]

yeah, i've read about bitwise stuff already.

 

the access types to the same board for a user is being stored in the same row, while access' to different boards for the same user is being stored in different rows.

 

thanks a lot again.

[DarkWater]

No problem.  Post back if you have any difficulties and I'll see if I can help.