Best Practice: Databases: Querys in DB or WebPage 1

[SUnderwood]

Hi

In breif, where is the best place to store a query definition when creating a DB driven web based application, in the web-pages through DW's database tools or in the database itself? (The database platform I will be using is Access, but in general I would believe the same rules should apply to all DBs)


Regards

Sean

 

[lebisol]

in the DB it self for "higher perforomance"
downside:
stored procedures are called.....if the procedure changes "your ASP code won't know about it".....if the DB gets corrupt..well...the same procedures are gone while you would still have your ASP pages if all the code was done in ASP.......
but since it's Access (which can use any performance improvements that you can get)I would use as many sotred procedures (DB coded) and just use ASP to call the executions.
take a look:

http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1

All the best!

> need more info?
:: don't click HERE ::

 

[SUnderwood]

Thanks for writing Lebisol.

You have confirmed my thoughts, and the link provided is very useful.

Storing queries in a database to me seems the best option. Network traffic is reduced, queries are already compiled, query paths can be configured (though maybe not for Access).

I am thinking it should also allow for much tighter security with access rights givenb to queries and not allow general SQL execution, and for much simplier programming of the interface code (ASP or otherwise).

Could you confirm this, and perhaps other benefits I haven't yet thought of?

Many thanks for your help

Sean

 

[lebisol]

ur absolttly right......use Access Groups(users) to dedicate one "web" user that is allowed to "do only web realted" querries and appropirate rights to the tables involved. As u can see, even if the DB changes most of the work you will have to do is export querries and some minor adjustements to the synthax.
The "secret" to this structure is that you comment your code in ASP pages since the code lengh is much less and keeping track of executions can be a pain....while ASP "code based" can be scanned easier and patterns of 'insert.delete.update' stand out.
If you need help setting up Access Rights let me know....and all the best!

> need more info?
:: don't click HERE ::

 

[lebisol]

LOL I am tired....why I am stuck on Access...anyhow, u get the idea!
Good Luck with the Project!

> need more info?
:: don't click HERE ::

 

[SUnderwood]

Thank for you help Lebisol.

Excellent advice!

I may take you up on that offer of assistance with access rights. I forsee I am going to have problems giving internet users access persmissions and assigning objects permission levels as I have, as yet, no concept of a) MSAccess security, b) web server and middleware security.

Perhaps we should start another "Best Practice: Databases: MSAccess Object Security for Web Development" Thread!?!
Or maybe, for new, we'll leave that until the time its needed.


Thanks again!

Sean

 

[lebisol]

Hi Sean!
Sure thing....you post it and I will share what ever knowledge I have.....and really, it is not that bad to set up it is just that the "concept" is overlooked.
also if u want it in PM: lebisol(at)hotmail.com
All the best!

> need more info?
:: don't click HERE ::

 

[lebisol]

lebisol2(@)hotmail.com

the other one got spammed beyound repair...
later,

> need more info?
:: don't click HERE ::