conception tips

Hi :)


i'm new to mysql and i'd like to know what's the best to store rights
in a mysql table. I have a user table, a media table (photos, videos),
and i want to have the ability to restrict access for some users. My
first idea was to create a table called rights with each user as a
field and each line for a media.

But this solution means each time i add a user or a media i have to
modify my rights table, is there a better way to do manage rights ?

Thx a lot :)

PS : if you have any good toturial to learn how to be efficient do not
hesitate ;)

>i'm new to mysql and i'd like to know what's the best to store rights
>in a mysql table. I have a user table, a media table (photos, videos),
>and i want to have the ability to restrict access for some users. My
>first idea was to create a table called rights with each user as a
>field and each line for a media.
>
>But this solution means each time i add a user or a media i have to
>modify my rights table, is there a better way to do manage rights ?

I'd consider making a rights table:

media
user
rights (e.g. read-only, read/write, etc.)

primary key (media,user)

To check rights for a particular {user,media} combination, look for
a row with matching user and media. If there is no such row, there
are no rights. Otherwise, the rights (e.g. read, write, delete, etc)
are listed in the table.

Another, less general, way to deal with this, is to assign groups
to users and media and rules for access. Your situation may or may
not fit into this well. Example:

Users have read/write/delete access to their own images.
Those in the admin group have read/write/delete access to everything.
Nobody else has write/delete access to anything.
Everyone has read access to "public" images.
Everyone in the same group as the user has access to
"semi-private" images.
The owner of the image and admins have the right to alter the
"public/semi-private/private" status of an image.

For this, an image has an owner, and a public/semi-private/private
flag. A user has a group, which is also used for the images he
owns. You can compute all access from that. This may not be
sufficiently general for what you want, but it doesn't require a lot
of detail in setting permissions.