RE: [PHP-DB] Database Design Recommendations
Eric, There are, as you will find out, a number of ways you could handle it. The right way is really your decision and it's directly related to the flexibility, maintainability, and security your site will require. I have had success in the past using a role-based permission system. For example, I'd create a role called Administrator and give that role permission to insert, update, delete, and select from every single table in the application/intranet. Then, I'd create another group called, for example, Newsreaders that only had permission to select from a table a with news items in it. They wouldn't be able to add or edit news items and they wouldn't be able to view any other information that didn't come from the newsitems table. When users logged in and were authenticated, they'd be assigned one or more of the roles that exist. And in the session framework, on each page that had to restrict access, I'd check the session variables $_SESSION[isadmin] or $_SESSION[isnewsreader] or whatever to make sure that they were members of the proper role to view that page. Of course, you need to build the Administrator interface that would allow the Administrator to assign users to roles in order to maintain all of the roles your application/intranet may need. You could do this on the command line in MySQL, but I think the extra effort to code it into a friendly web page would pay off in maintainability and usability by someone other than yourself. Now, this may not be the most secure way of controlling access, but it was appropriate for the application I was working on. You may also want to do a Google search and see what kinds of pre-build session handling tools are available. That may save you the time of building one from scratch and troubleshooting it. Good luck. Rich -Original Message- From: Eric Cranley [mailto:[EMAIL PROTECTED] Sent: Monday, November 01, 2004 5:36 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Database Design Recommendations I tried to find this in the archives. If I missed it, my apologies in advance. I'm developing an intranet for my company, and we are planning on putting sensitive information on it. I've already setup a login system using sessions, but I'm not sure how to manage and store permissions, such as who can view commissions, reset user passwords, etc. I've devised two methods, but I have a feeling there's a better way to do this that I'm not thinking of. I'll be storing these permissions in a MySQL database. My first idea was a single field with the SET datatype. This allows for a user to have multiple permissions, but makes it hard for other employees to add permissions later, if they decide to restrict a previously open access page. (I should mention that I'm the only person here who knows how to adjust a table in MySQL, and I won't be around forever.) My other idea solved the previously mentioned problem. I could create a second table with employee permissions. It would have two fields, employee_id and permission. Every employee would have one row for every permission they had. I could also create a third table of page names and required permission to view it, so if someone later decides that only certain people should view a page, they can change it without coming to me. What do people think of these ideas, and is there a better way to do this? Thanks in advance. Eric Cranley IT Specialist Willis Music Company -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Database Design Recommendations
Eric, Your second approach is fine. It's denormalized, extensible, and can be manipulated using tools you put in place. You may want to consider groups as well, thus people belonging to a group could view/edit pages, that has the potential to save a lot of administrative scut work. A table of permissions will also be required so that the various codes used for differing levels of permission are consistent. Suggest that when a user logs in the appropriate permission levels etc. should be fetched and stored in a session to save on some trips to the server. This session data, creatively used, could mean that only the files/pages that user is authorized for will be displayed. Miles At 06:36 PM 11/1/2004, Eric Cranley wrote: I tried to find this in the archives. If I missed it, my apologies in advance. I'm developing an intranet for my company, and we are planning on putting sensitive information on it. I've already setup a login system using sessions, but I'm not sure how to manage and store permissions, such as who can view commissions, reset user passwords, etc. I've devised two methods, but I have a feeling there's a better way to do this that I'm not thinking of. I'll be storing these permissions in a MySQL database. My first idea was a single field with the SET datatype. This allows for a user to have multiple permissions, but makes it hard for other employees to add permissions later, if they decide to restrict a previously open access page. (I should mention that I'm the only person here who knows how to adjust a table in MySQL, and I won't be around forever.) My other idea solved the previously mentioned problem. I could create a second table with employee permissions. It would have two fields, employee_id and permission. Every employee would have one row for every permission they had. I could also create a third table of page names and required permission to view it, so if someone later decides that only certain people should view a page, they can change it without coming to me. What do people think of these ideas, and is there a better way to do this? Thanks in advance. Eric Cranley IT Specialist Willis Music Company -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Database Design Recommendations
At 23:36 01-11-2004, Eric Cranley wrote: I tried to find this in the archives. If I missed it, my apologies in advance. I'm developing an intranet for my company, and we are planning on putting sensitive information on it. I've already setup a login system using sessions, but I'm not sure how to manage and store permissions, such as who can view commissions, reset user passwords, etc. I've devised two methods, but I have a feeling there's a better way to do this that I'm not thinking of. I'll be storing these permissions in a MySQL database. My first idea was a single field with the SET datatype. This allows for a user to have multiple permissions, but makes it hard for other employees to add permissions later, if they decide to restrict a previously open access page. (I should mention that I'm the only person here who knows how to adjust a table in MySQL, and I won't be around forever.) My other idea solved the previously mentioned problem. I could create a second table with employee permissions. It would have two fields, employee_id and permission. Every employee would have one row for every permission they had. I could also create a third table of page names and required permission to view it, so if someone later decides that only certain people should view a page, they can change it without coming to me. What do people think of these ideas, and is there a better way to do this? Thanks in advance. Eric Cranley IT Specialist Willis Music Company Firstly, the built-in PHP sessions are not the best to secure sensitive information because of how they generate and maintain the sessionIDs, they're fine for keeping information between page scripts, but not recommended for controlling access to anything. I always recommend against using them for anything that requires login. Instead, my recommendation, and that of several others, is to use a session table in the database, where the sessionID you generate is paired with the actual userID, and the sessionID is then sent to the user in a cookie. How to generate the sessionIDs are really up to, the important part is that they're unique. In my systems, I use the user's loginID, the user's browser agent ID, as well as other gathered information, then piece all of this together into 1 string, MD5 it, then add a very long string of random characters to it, md5 it again, and then chop out 256 chars, and use those as the session ID. The ID is then stored in the session table, with the userID, login time, login IP, and other data needed for security purposes. It also has a field for last action time, which is updated on each page load. When a user is inactive for 1 hour (in my case, you can reduce this to 20 mins or less, although going under 20 mins is not recommended as it may be impossible to read a page without having to login again to view the next), the ID expires. If the same user logs in from a different browser session, a new session ID is generated, and the old ones killed and terminated, making all the other sessions invalid. Every time a user logs in, and out, this information is logged. On every login and manual logout, the system clears the session table for all session IDs for that user. The logged information is stored in seperate table. The system I made checks the cookie against the active sessions, and lockout the page entirely if it can't find a match. Meaning all it loads is a login box, the reduced menu for non-logged in users, and a You do not have permission to view this page. I also make my scripts so only the parts of a page or form that the user can actually use is generated and loaded. Thus they can't see the options they don't have permission to use. This reduces the risk of people knowing that a certain functionality exists, and thus try to gain access to that functionality. Secondly, having a boolean field for each perm in the user table is my recommendation. You can also do group based perms, where you have a table with a row for each group, and a boolean for each perm that group can be set to. Or a combination of both. I use both user and group based perms, where the user allow settings override the group's not allowed settings. In my forum system, I've got a per forum based perm system, where each forum has a row for each group in a seperate table. This means that each group can have different perms for each forum. This method is easily adapted to other kinds of systems. Not sure this helps, but hope it does. Personally I would not use any publicly known method of maintaining sessions for anything containing sensittive information. The risk of someone figuring out how to circumvent it is too great. My forum/community system login covers large amounts of personal information, as well as private correspondance. I've made it so that even the system administrators do not have access to any of this information unless the users grant their profile access to it. Only way to see it would be to go
Re: [PHP-DB] database design question
i would add a second table and also store the totals in the lyrics table like you suggested then you can do things like top 10 this week / top 10 this month and make sure its not being abused LYRICS_RATINGS - id | lyric_id | rating | vote_date | remote_addr same idea for hits/impressions olinux --- John Ryan [EMAIL PROTECTED] wrote: ive a table called 'lyrics' which is the main content on my site, song lyrics. im thinking of introducing rate this lyric and all that kind of stuff into the site. would it be better, from a design point of view, to create a second table for lyrics ratings and hits or just add a 'ratings' and 'hits' field names to the exisitng table?? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Database Design Issue
Yes, songs do span shows. However, the list of songs for each weeks show starts out as a *.asc (same a *.txt) space-separated file. This gets uploaded via a PHP file that formats each row and inserts it into the database. That's why even though I know there will be duplication it appears (to me at least) I'll just have to live with it. I have no guarentee that the songs will appear in the *exact same way* every time either (you really don't want to know why -- I'm getting a migrain just thinking about it). I've attached an example of the incoming files I have to deal with for your reference. Also, I'm just designing the website I will have no part in the everyday maintenance of the website -- except when the maintainer gets stuck. :) Jason CPT John W. Holmes wrote: From: "Jason Lange" [EMAIL PROTECTED] Here's my question. I'm creating a website for a weekly radio show. One of the items I would like to store in the database is the list of songs played in each show (there's normally between 20-25 songs per show), and the songs will be displayed by show. Should I put all of the songs in a single table with a field denoting what show they're from, or should I create a separate table for each weeks show and just request that entire table? I'm looking at this from both a long-term performance and *maintenance* issue. The first question is if songs can span multiple shows? If so, you need 3 tables. One to hold the songs, each song getting a unique id. One table to hold the shows, each show getting a unique id, and finally a third table that relates the two. You'd list the song_id and the show_id that it's used in. Add in a row for each song used in each show. If a song is not shared across shows, then you could go with two tables. A show table where each show gets a unique id and a song table with a column that has a show_id in it relating it to what show it's used in. You'd be better off with the first method, though, because sooner or later down the line there's going to be a song shared, I imagine. :) ---John Holmes... FOREVER WE WILL SING MICHAEL W. SMITHWORSHIP *DVD* (REUNION ) YOUR LOVE OH LORDTHIRD DAY OFFERINGS: A WORSHIP ALBUM (ESSENTIAL ) ENOUGH CHRIS TOMLINOUR LOVE IS LOUD (SPARROW ) LORD YOU'VE BEEN GOOD TO ME GRAHAM KENDRICK WHAT GRACE (FURIOUS ) LET THE RIVER FLOW DARRELL EVANS LET THE RIVER FLOW (INTEGRITY ) MY GOD REIGNSDARRELL EVANS LET THE RIVER FLOW (INTEGRITY ) I LAY ME DOWNDARRELL EVANS FREEDOM (VERTICAL ) I AM IN LOVE WITH YOUDARRELL EVANS FREEDOM (VERTICAL ) BREATH OF GODCHRISTY NOCKELS GIRLS OF GRACE (WORD ) SPOKEN FOR MERCY MESPOKEN FOR (INOTOF) PRAISES BE LINCOLN BREWSTERAMAZED (VERTICAL ) LORD OVER ALLSONIC FLOOD RESONATE (INO ) HUNGRY KATHRYN SCOTT HUNGRY (VINEYARD ) BE GLORIFIED TIM HUGHES ALL AROUND THE WORLD (WORSHIP T ) LORD YOU HAVE MY HEART DELIRIOUS CUTTING EDGE (SPARROW ) TRADING MY SORROWS DARRELL EVANS FREEDOM (INTEGRITY ) FIELDS OF GRACE DARRELL EVANS ALL I WANT IS YOU (VERTICAL ) ALL I WANT IS YOUDARRELL EVANS ALL I WANT IS YOU (VERTICAL ) WE WILL EMBRACE YOUR MOVEDARRELL EVANS LET THE RIVER FLOW (INTEGRITY ) LORD REIGN IN ME VINEYARD U.K. WINDS OF WORSHIP 12 (VINEYARD ) YOU SHINEBRIAN DOERKSEN YOU SHINE (INTEGRITY ) GOD OF WONDERS CAEDMON'S CALL IN THE COMPANY OF ANGELS (ESSENTIAL ) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Database Design Issue
From: Jason Lange [EMAIL PROTECTED] Here's my question. I'm creating a website for a weekly radio show. One of the items I would like to store in the database is the list of songs played in each show (there's normally between 20-25 songs per show), and the songs will be displayed by show. Should I put all of the songs in a single table with a field denoting what show they're from, or should I create a separate table for each weeks show and just request that entire table? I'm looking at this from both a long-term performance and *maintenance* issue. The first question is if songs can span multiple shows? If so, you need 3 tables. One to hold the songs, each song getting a unique id. One table to hold the shows, each show getting a unique id, and finally a third table that relates the two. You'd list the song_id and the show_id that it's used in. Add in a row for each song used in each show. If a song is not shared across shows, then you could go with two tables. A show table where each show gets a unique id and a song table with a column that has a show_id in it relating it to what show it's used in. You'd be better off with the first method, though, because sooner or later down the line there's going to be a song shared, I imagine. :) ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Database design
Shaun, I took a quick look at your database layout and noticed that Practice_ID and Clinical_Trial_ID are repeated in the Booking table. This isn't necessary because the Booking table links to the Project table which contains this information. In the Project table, Practice_ID and Clinical_Trial_ID should probably just be foreign keys and not primary keys to that table. Same thing for User_ID and Project_ID in the Booking table. To keep track of the data you will need to create a new table which is linked to the Clinical_Trial table. For instance you could create a table called Clinical_Data which has the following fields: * Clinical_Data_ID (PK) * Clinical_Trial_ID (FK) * Data_Description - field that describes a piece of collected data (e.g. blood pressure before hypnosis) Is the User table where the client information goes? You also need to create a joining table between the User (or whatever table contains the client info.) table and the Clinical_Data table. -- Beverly Steiner [EMAIL PROTECTED] -Original Message- From: shaun [mailto:[EMAIL PROTECTED] Sent: Monday, March 10, 2003 8:53 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Database design Hi, I am creating a database for a web application. The idea for the application is for a company administrator to be able to log into the site and allocate staff to a project which will be a clinical trial at a particular practice. Staff will be able to log in and update the status of the project they are working on and clients will be able to log in and book a member of staff. Administrators will be able to add / edit / delete - staff / clients / clinical trials. Here is my database diagram: http://www.mania.plus.com My problem is each different clinical trail will need to have different data collected for it. How could I handle this in the database model? The administrator would like to be able to edit the data collected for each trial via the site... Any other comments on my database model would be appreciated as this is my weakest area of web development by far! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
