To DB or not to DB?

[keith73]

Greetings,

I just started working at a company where I'm taking over for someone who wrote some horrendous looking code. There are some thins I'd like to ask for your opinions on.
Here's a brief synopsis of the app.
USer goes to site, if a cookie is set, they skip the login form. If not, they have to enter username/password.

1)The current code has a check for the cookie on every page, which is not so unusual, what is unusual is that on every page, a call is made to the DB to authenticate the user. In apps I've written before, I authenticate from the user/pass combo in the DB once, then create a session and then on each page I validate the session.
- Which method do you think works better?

2) Cookies, the current code uses cookies, but stores no info other than the username. It uses the username to then call the DB (as described above) and get all info about the user, such as access permissions. There are a lot of things that I feel should be in a session. The access permissions, user type, etc. So that when they click on a page, that data is already stored in a session and no DB call is required to retrieve it.
- Which method do you think works best?

3) There are pages of CGI code that need to be called depeding on the equipment the user is using. These cgi calls are stored in BLOB fields in the DB. The consensus here is that they would be better off in include files, rathter than calling the DB everytime.
- Would an include file be less taxing on the server than a call to the DB.

4) Dates - currently, dates are being stored as epoch dates (INT) which I find strange. I asked the sysadmin about using date or datetime instead, but he thought it was less load on the DB to store as INT and then have PHP convert the date than to store as DATE and have the Query perform Date Calculations.
- Which method would you use and does it really cause that much more load on the DB if you do date calcs in the query?

Thanks for your opinions. It will help me make a better decision and allow me to be better prepared when I bring these issues up to my colleagues.

• keith

[bastien]

1)The current code has a check for the cookie on every page, which is not so unusual, what is unusual is that on every page, a call is made to the DB to authenticate the user. In apps I've written before, I authenticate from the user/pass combo in the DB once, then create a session and then on each page I validate the session.
- Which method do you think works better?

defintely use sessions, one trip to db to autheticate, store relevant needed data in the session

2) Cookies, the current code uses cookies, but stores no info other than the username. It uses the username to then call the DB (as described above) and get all info about the user, such as access permissions. There are a lot of things that I feel should be in a session. The access permissions, user type, etc. So that when they click on a page, that data is already stored in a session and no DB call is required to retrieve it.
- Which method do you think works best?

again sessions to store the data

3) There are pages of CGI code that need to be called depeding on the equipment the user is using. These cgi calls are stored in BLOB fields in the DB. The consensus here is that they would be better off in include files, rathter than calling the DB everytime.
- Would an include file be less taxing on the server than a call to the DB. yes...no connections need to be made, data to be retrieved etc....includes are much better here

4) Dates - currently, dates are being stored as epoch dates (INT) which I find strange. I asked the sysadmin about using date or datetime instead, but he thought it was less load on the DB to store as INT and then have PHP convert the date than to store as DATE and have the Query perform Date Calculations.
- Which method would you use and does it really cause that much more load on the DB if you do date calcs in the query?

i tend to use date/timestamp in the db, but it does depend on the application, never use INT though, usually date if not timestamps

hth

[hand]

ad 1)
I would prefer the second method, validating against session

ad 2)
For security reasons the cookies should only store the session-id instead of the username. If you once read the user data you can store them to session variables. I see only one disadvantage for this method, if some admin changes some permissions during a user is logged in. But it depends on the application if this circumstrance could be a fact to always read data fom db.

ad 3)
It depends on the application. If you are storing these data in the database the whole application gets more portable.

ad 4)
DATETIME and DATE are the best methods to store date/time values. These formats supports a lot of functionalty. I don't understand why somebody stores dates as integer in unix timestamp format.