I'm implementing a user authentication scheme with PHP/MySQL.
I would like to be able to assign users to more than one group, but I have no idea how to implement that. I know how to design the DB such that users are assigned to only one group, that's easy.
So far, I have these tables:
user
----
userid
username
password(md5)
# and some other fields
group
----
groupid
permissionid - foreign key
groupname
# and some other fields
permission
----
permissionid
read - enum ('Y', N')
write - enum ('Y', N')
delete - enum ('Y', N')
edit - enum ('Y', N')
create - enum ('Y', N')
Would I need a fourth table? What happens when a user's permissions overlap and one permission set allows for write privileges and the other permission set does not?
Any help is very much appreciated.
