Database layout - opinions?

[Rafkin]

I am working on a php script. I still need to do the php part, but when I get that far, I'll take this to the php section of the board. For right now, though, I'm trying to figgure out the best database layout to use.

I've not really done a complex database layout before, though I have seen one done for another site, in wich I was able to help with the coding and figguring out way that site was going to work. As such, I managed to come up with this layout for what I need, but lack confidence in my own work, and am asking for suggestions or help in making sure I do this right.

If I can get this script to working, I will be making it available, for free, via several php script archives. So flexibility, and portability are issues I am dealing with here. (unless I just simply can't make it flexible or portable, at wich point it'll be just for my personal use, though I'd really like to be able to share it.)

First off, let me explain what the script is. It's sort of a session handling, authorization, user management script. Basically, It will let users register to the web site (I'll get into the email verification etc. when I go to the php forumn), and once registered, be automatically assigned to a user group.

This script will use both user groups and flags, to determine access to different parts or features of the web site. For this use, a group will be nothing more than a bunch of pre-set flags, so that one can work with groups of people easily. The admin would then be able to set private flags per person to overwrite any group flags.

Access to a page or feature will need to check for the flag first on the users level, wich can be either True (allowed), False (not allowed) or non-existant. If non-existant, it should then check the user's group, and if non-existant there, go to the default.

(in all actuality, I'm probably going to have it fill in defaults into an associative array first, then overwrite with group flags, then overwrite again with user flags.)

The database schema I have currently is this:

Users

User ID - Personal user ID number
Group ID - Group the user belongs to
User Name - Self explanitory
User Pass - encrypted
User Email - Self explanitory
Last Visited - For last visited line
LoginID - Sort of an extended sessionID. I've used this before.

User Groups

Group ID - unique group ID
Group Name - Self explanitory
Group Desc - Short description of the group

Flag Groups - This is to group like flags togeather when editing. eg: calendar editing, writting, and viewing flags would all go under calendar group. It's just for convenience of the admin.

Flag Group ID - unique ID
Flag Group Name - Self explanitory
Flag Group Desc - Short descriptioin of the flag group

Flags

Flag ID - All these freakin ID's. Sheesh!
Flag Group ID - Wich flag group it belongs to.
Flag Name - Everything has got to have a name.
Flag Desc - Short description of the flag.
Default - True or False. This is where the default value will be set.

And finally

Flagsets - still looking for a better name to call this. This is where I tie users and groups to flags.

Line ID - Will seldom get used, except to edit, or delete a setting.
User or Group ID - The ID for the user or group this flag is setting. User ID's start with U, Group ID's start with G
Flag ID - The flag being set
Value - True or False.

Will this be sufficient? should I do anything different for optimization? I am wondering if I should set some of the user details to a different table, in case I want to add more fields to keep track of per user?

Thanks in advance for any opinions or idea's.

[idx]

What about a user that is a member of multiple groups?

Is that where these flags come in?

-r

[Rafkin]

What I had thought was, to have each user a member of only one group. Again, the group is just a set of pre-set flags, to make managing more then one person at a time easy. The difficulty I see in having a member part of more than one group would be, conflicting flags. If one group is denide access to one flag, while another is allowed access. wich do I go with?

That is why flags are set on three levels. Default, Group, and User.

When access to a flag is checked it should check the user level first, and if not there, check the group level, and if not there again, go to default. At each level the flag could be set as True, or False (allowed or not allowed). Upon finding either one, the search would stop at that point.

I'll even put in an option for the admin, when switching a user from one group to another, to clear all user flags or leave them in, at his discretion.

I've decided to go with the schema i've got laid out above, may change it as I go. Now I'm gonna try to figgure out all of the php scripting needed to use the tables effectively.

[idx]

Sounds like more trouble than it's worth. There's always phpGACL and seagull to use unless you just want to write it.


The way I've structured things in the past is that each public function is defined group access. eg: So only members of "foo_add" can execute it. All users are part of the "users" group, but they can also be assigned to others as well.

Most of my applications required something more fine grained, so I ended up having more validation code anyway. (eg: user can excute the delete function in the FAQ class, but only on items they created or were specifically assigned to..)

-r

[idx]

I forgot to mention LiveUser as well. Looks pretty slick and doesn't seem to be as difficult as phpGACL.

-r