It is the intention of this article to discuss and generate further debate surrounding best practices in developing database driven, session orientated dynamic websites and to present some adopted styles and conventions. The ideas and approaches that are developed here could be realised with almost any combination of server side scripting language and relational database.
Sessions and relational databases both offer the opportunity of storing information server side. Sessions can help to maintain state while you navigate an internet website and relational databases offer a medium for the permanent storage of information in a logical and consistent manner which allows for structured queries to be carried out against that stored information.
During the design process the dilemmas that are often encountered are deciding how information should be stored in a database and what information should be registered to the session.
What I would like to demonstrate now is a framework of cooperation between relational databases and sessions which is both aesthetically appealing as well as productive this will be done by examining features of a secure website.
First of all I would like to discuss in simple terms what it means for a table to be normalised. A table is considered normalised if information is organised logically without inconsistencies and there is minimal redundancy an example will help to demonstrate.
Consider the following simple normalised table called security, this table stores sensitive information and is queried against to gain authorisation to a secure website.
   username     password     access_level  screen_name   security_id


   rstallman     r5y8jv5f8      U            richard         1

   billgates     8yc2d42f5      X            bill            2

   linustorvalds h89x35f56      P            linus           3

Notice that this security table encapsulates all that is security related there is no redundancy, that is no unnecessary repetition and if you had to update a username or a password you have only to make a single change somewhere. This is normalisation.Also note that all records here in the security table have a unique identifier called security_id this in a important field called a "primary key".
To gain access to this secure internet website a user has to supply a username and password in a form this then results in a php block being executed when the script actions itself :

<?php

 
if(isset($_POST['submit']) ) {     // true if form has been submitted

    
session_start();

      include(
"inhouse_functions.inc");       
          
connect_database("secure_online");    // obtain access to database   


    
$SQL=" SELECT security_id FROM security
 
            WHERE username='$username' AND password= '$password' "
;

                                 
              
$security = @mysql_query($SQL);


                 if ( 
mysql_num_rows($security) ) {

                     
$row mysql_fetch_array($security);  

                         
$security_id $row['security_id'];


                               
$sess_security_id $security_id ;                     
                                  
session_register('sess_security_id');


                                      
header("location: entry_page.php") ;
                 }

         
// authorisation not successful show form again....                        
}

?>

<html>
 ..

<form action = "<? echo $PHP_SELF;?>" method="post">

     ... rest of the form to collect username,password and submit button

<type="hidden" name="submit" value=1>

</form>
</html>
All other pages on this secure internet website then can check to see that authentication has occured by checking if the primary key $sess_security_id has been registered to the session :

<?php
 
if( !session_is_registered('sess_security_id') ) {

            
// the security_id key is registered with the session
            // on authorisation 

      
header("location: index.html ");     // send to authentication page ..

}   
?>
A nice feature here is that nothing sensitive is registered to the session and the value stored has the potential of reconstructing any security information the subsequent scripts might demand such as checking access level privileges (U,X or P) ....

<?php
$SQL
=" SELECT access_level FROM security 
                
           WHERE security_id='$sess_security_id'  "



  
$security  mysql_query($SQL);

      
$row mysql_fetch_array($security);  


          
$access_level $row['access_level'];


             if(
$access_level == 'U') {

                     
// content sutiable for access level U
             
} ..

....
?>
Lets suppose now we have another table called memos which stores memos written by users of this secure website where access level privilges dictate who can update a memo.When this table is normalised it will look something like this:
   relSecurityID    details                  date     time    memo_id

      2             what is gnu/linux ?      020823    1426     1
      6             heard of open source?    020823    1705     2
      4             dependencies!!!          020824    0852     3
In this we don't need to store the access level privileges or the name of the person who writes/updates the memo all we need to store is the security_id, stored under relSecurityID (foreign key) in this table, because this allows us to potentially reconstruct any security information we may need.
For example suppose an authorised user is searching/scrolling through a screen of memo extracts on his browser from which he can select to see the full memo by clicking on a hyperlink, it will be necessary to trap the memo_id and pass it to the next page this will then allow to reconstruct the complete memo and potentially any associated security related information through the foreign key relSecurityID:

<?php

   $SQL
=" SELECT details,date,time FROM memo   "

    
$memo mysql_query($SQL);


       while(
$row mysql_fetch_array($memo) ) {     // produce all memos extracts....

               
$details $row['details']; 
                
$date $row['date'];               .... // etc
?> 
                  //    display a particular memo extract..
                  //    hyperlink...

      <a href="update_memo.php?memo_id=<? echo $memo_id?>"> memo </a> 


  <?     }   ?>
On reaching the update memo page I would then consider registering the memo_id to the session ($sess_memo_id) as then you can potentially reconstruct that last memo read/updated using previous techniques.
Once on this page you may need to determine whether the memo information displayed in a text box is read only by checking access level privileges of the person who wrote the memo against those of the person currently viewing the memo. We have seen how to determine the access level privileges of the current authorised user, to determine those of the user who wrote the message we constuct a query like the following:

<?php
 $SQL 
" SELECT access_level FROM security,memo 

             WHERE security.security_id = memo.relSecurityID

                AND memo_id = '$sess_memo_id' "  
;
?>
This query will join the information for the particular memo with its associated information in the security table to determine access level privileges of the person who wrote the message ,other related information could be extracted if required by extending the select statement.
Take time to understand the above SQL query, if you are not familiar to joins, as extracting information from normalised tables in this way is an important skill to master,
      ________________________        ____________________________
     |  X        security_id  |       | relSecurityID     memo_id  |
     |________________________|       |____________________________|

        security record                   memo record
it's kind of like a game of dominoes where a particular security record can connect with many memo records providing security_id and relSecurityID match !
To summarise then registering keys to the session seems a good practice as they serve to give a sense of orientation to the user currently navigating your website. All transient information which does not maintain state I prefer to pass from page to page until it's no longer required, ie inserted into a table. However this is a broad generalisation of how I go about doing what I do, sometimes I will register non key information to the session such as 'screen_name' if every page shows the current logged in user and I will sometimes introduce repetition/redundancy into a database table if I am sure that it will never have to be updated manualy, ie some generic names in a table such as 'su' , 'administrator', etc...
Finally this has been an open exposition of how I go about doing what I do as a php/mysql developer,it has not been my intention to indoctrinate anyone in best practices rather to open the debate on what people consider to be good practices and what there rationale is. All developers clearly needs to have a coherent system of beliefs, a platform from which they can begin and make decisions and all belief systems stand or fall on whether on not they can produce concrete results, at the moment this simple outlook of mine is proving helpful.
paulsbooker