Page 3 - Best Practices : ASP Coding, SQL Statements, and Database Structuring

Very nice thread indeed.
Good job bslintx, this thread is very informative for newbies and some of us who have a bit more time making asp code. Thanks for the refresher.
I'd give you points but system won't allow it, as soon as I can, they're yours.

This thread is very useful as a guideline, what to do and what not to do

Comments are your friend. They can also be your enemy.

So not only comment your code, comment it properly. Don't say "what" - that should be apparent from the code. Say "why" - not always so obvious more than 6 months down the line.

consider the following, harmless looking code

No Comment


Bad Comment: (I used to work with a programmer who did comments like this all the time)



Better Comment:


Even Better Comment


Ok this is a trivial example, but in a system with 1000s of lines of code thy make a huge difference.

Elija

Avoid Redundant Code

Redundant code refers to code blocks that are identical or nearly identical in different parts of an application. Redundant code adds processing load on the server side because an ASP page must be compiled each time that it is requested from the server. And the more code there is to compile, the more processing time is used. Also, redundant code is hard to debug and maintain because such blocks of code generally must be edited separately each time a change is made (even though they all perform the same basic process).

Subs and Functions

If you can identify identical or similar code blocks in your application, consider converting these code blocks to Subs or Functions. Subs and Functions can take parameters so it doesn't matter if the code blocks do similar things; you can create parameters that enable the functionality of Subs and Functions to be extended. In addition, by combining similar code blocks into Subs and Functions, debugging and extending the code can be done more easily. Rather than having to find all the similar code blocks and edit each separately, only one Sub or Function needs to be edited.

Objectives of Good Relational Database Design:

There are many distinct objectives that you must achieve in order to design a good, sound, structured database. You can avoid many of the problems you may encounter by keeping the following objectives in mind and constantly focus on these whilst designing your database.

• The database supports both required and ad hoc (unplanned) information retrieval. The database must be designed to store the data necessary to support information requirements defined during the design process and any possible ad hoc queries that may be posed by the users.
  
• The tables are constructed properly and efficiently. Each table in the database must represent a single subject only and should be composed of relatively distinct fields which keep redundant data to an absolute minimum and should be identified throughout the database by a field with unique values.
  
• Data integrity is imposed at the field, table and relationship levels. These levels of integrity help guarantee that the data structures and their values will be valid and as accurate as possible at all times.
  
• The database should support business rules relevant to the organization it is designed for. The data must provide accurate information that is always meaningful to the business.
  
• The database should lend itself to future growth and development. The database structure should be easily modifiable and expendable as the information requirements of the business continue to change and grow.

You may find it difficult at times to fulfill all of these objectives, but you'll certainly be pleased with your final database design structure once you've met them.

Benefits of Good Database Design:

The time that you invest in designing a sound, well strucuted database is time well spent. Good database design saves you time in the long run because you do not have to spend time constantly revamping a quickly and poorly designed structure. You gain the following benefits when you apply good design techniques:

• The database structure is easy to modify and maintain. Modifications you make to a table or field will not adversely affect other tables or fields in the database.
  
• The data is easy to modify. Changes that you make to the value of a given field will not adversely affect the values of other fields within the table. Furthermore, a well-designed database keeps duplicate fields to an absolute minimum, so you typically modify a particular data value in one field only.
  
• Information is easy to retrieve. You should be able to create queries easily due to well constructed tables and the relationships between them are correctly established.
  
• End-User applications are easy to develop and build. You can spend more time on programming and addressing the data manipulation tasks at hand, instead of working around the inevitable problems that will arise when you work with a poorly designed database

MUCH MORE TO COME.........

Naming Tables and Fields

Once we've reached this point, let's establish our table and field names. Names for all database elements should be:

• Unique
• Clear, meaningful, unambiguous
• Short

Some restrictions for naming tables:

• No acronyms or abbreviations
• No proper names or words which unduly limit the data which can be entered
• Should not imply more than one subject
• Should be plural

And for naming fields:

• No acronyms
• Use abbreviations only if clear and meaningful
• Should not imply more than one subject
• Should be singular

You'll probably see some duplicate field names, such as "Name" in both the "Companies" and "People" tables. Let's make them unique across the database. You might choose "ContactName" and "CompanyName" but whatever you use, stick to the guidelines above, and be consistent

Good posts by bslintx but would just like to add:

Subs and functions can not only be used to remove redundant code, that can also be used to simplify code.

Move functional blocks of code to subroutines and functions and give them meaningful names. This will turn your main program logic into an overview of the program and clarifies the program flow, makes it easier to debug and change.

Elija

This thread is very useful but can you all give more info on database structure and sql joins.

appreciate the response dev...would be great to share your knowledge on this subject too...join on in...again thanks!

I am still in learning process and trying to learn as much as possible. All your great thoughts are food for thought for me. thanks again.

no problem dev....i'll work on that soon...thanks for the insight

Use queries as their intended... Their are many different options for your query rather than.. Select * From Table

Here is a good link with some query examples and some comments about them...


http://www.l-and-b.dk/asp/sql.asp

Access Queries


btw Good Thought bslintx

Caching ADO Connections is usually a bad strategy. If one Connection object is stored in the Application object and used on all pages, then all pages will contend for use of this connection. If the Connection object is stored in the ASP Session object, then a database connection will be created for every user. This defeats the benefits of connection pooling and puts unnecessarily high stress on both the Web server and the database.

Instead of caching database connections, create and destroy ADO objects on every ASP page that uses ADO. This is efficient because IIS has database connection pooling built in. More accurately, IIS automatically enables OLEDB and ODBC connection pooling. This ensures that creating and destroying connections on each page will be efficient.

Since connected recordsets store a reference to a database connection, it follows that you should not cache connected recordsets in the Application or Session objects. However, you can safely cache disconnected recordsets, which don't hold a reference to their data connection. To disconnect a recordset, take the following two steps:

When you are done using a recordset, say after painting a table with its data, release it immediately, rather than waiting until the end of the page. Setting your VBScript variable to Nothing is a best practice. Don't let the recordset simply fall out of scope. Also, release any related Command or Connection objects. (Don't forget to call Close() on recordsets or Connections before setting them = Nothing.) This shortens the time span in which the database must juggle resources for you, and releases the database connection to the connection pool as quickly as possible.

Use Option Explicit in your .asp files. This directive placed at the top of the .asp file forces the developer to declare all variables that will be used. Many programmers consider this helpful in debugging applications, as it avoids the chance of mistyping a variable name and inadvertently creating new variables (for example, MyXLMString=... instead of MyXMLString=).

Perhaps more important, it turns out that declared variables are faster than undeclared variables. Under the covers, the scripting run time references undeclared variables by name, every time they are used. Declared variables, on the other hand, are assigned an ordinal, either at compile time or run time. Subsequently, declared variables are referenced by this ordinal. Since Option Explicit forces variable declaration, it insures that all variables are declared and thus will be accessed quickly