Code Forums - View Single Post - Database layout

Rafkin · 12-17-2004, 05:57 PM

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

Code:

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

Code:

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.

Code:

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

Flags

Code:

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.

Code:

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.

12-17-2004, 05:57 PM	#1 (permalink)
Rafkin Registered User Join Date: Mar 2004 Posts: 20	Database layout - opinions? 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 Code: 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 Code: 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. Code: Flag Group ID - unique ID Flag Group Name - Self explanitory Flag Group Desc - Short descriptioin of the flag group Flags Code: 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. Code: 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.