Database Development Services and Web Development Services

Preamble

Database-driven web development survived the general decline that hit the Internet industry at the start of this century, and is now one of the most important and fastest growing branches of IT. The time and cost benefits that come with web-based applications continue to stimulate increasing interest as large, medium and small businesses inevitably realise its importance.

In most cases, when we talk about dynamic web applications, we mean database-driven web applications, as databases offer the most reliable, flexible and feature-rich means of data storage and handling. During the last few years, database engine developers have wasted a considerable amount of effort making their products intuitive and easily learned. Although they have significantly simplified developers’ tasks, effective database development still requires a certain level of expertise.

Preface

Computing means calculation. Calculation results in data. Data has to be stored, or it will be lost when you switch off your computer.

A database is, however, much more than an information repository. Modern databases are integrated with powerful tools (database engines) that allow their users to handle data in different ways: for example, to organise it, and to retrieve portions of it at any time. While delivering the requested information, the inner mechanisms must work quickly and efficiently, thus providing the database-driven application with the most sophisticated results.

It should not be assumed that databases have always been like this. Flat files, DBF files, the Paradox Engine … these are just some of the numerous milestones databases had to pass before they became what they are now.

Introduction to databases

A short history of the database

Generally speaking, any collection of data can be called a database. From this point of view, flat files we are so well familiar with can be regarded as the first databases of the computer era. Of course, they lack many essential features:

• It is not easy to retrieve a particular record from such a “database”, as a flat file is just a set of bytes, and the application itself has an inflexible structure and consistency.
• When anything is broken (say, if one byte from the sequence is missing), the whole thing is messed up and further data handling becomes impossible. Your application will produce any sort of unexpected results, unless you make your algo smart enough to detect and correct such data errors automatically.

So, although some flat files (for example, .ini files) are still used to store and retrieve certain data at runtime, more powerful mechanisms were strongly needed. And they appeared. Until recently, they were known as “database engines”.

The 1960s were marked by a rapid development of various database concepts and database engines, most prominently the network model. This model dominated the database engine market until the early 1970s. Then E.F. Codd suggested a “relational model”, with which he won the famous Great Debate, and the relational model became the acknowledged basis for the majority of database engines worldwide.

In the mid-1990s the appearance of the WWW gave a new impetus to databases. Dynamic web applications swiftly turned into one of the most rapidly growing industries of the modern world, and new tools, including new database engines, quickly saturated the market. This process still continues, and there are reasons to believe that database web development still has a long way to go before it faces any signs of decline.

Basics and tips

A relational database stores all the data in tables. Every data attribute is represented by a column, while every separate data entry forms a record and is represented as a table row. Any table cell contains some value which can be retrieved whenever necessary with the use of the built-in data-handling mechanisms. Tables are connected to each other via foreign keys.

Data values belong to different types. These include INTEGER, FLOAT, DOUBLE, CHAR, VARCHAR, and BOOLEAN, to name a few. Various database engines support various sets of data types.

When you do not need to use all the attributes or records, it is possible to create subsets. These subsets are nothing but other tables. We will discuss this in more detail when describing the basics of Structured Query Language (SQL).

When you are planning a new database-driven application, be it a desktop Windows program or a web site, the first thing to do is to design your database, that is, to assign your future data to tables, to determine relations between those tables (foreign keys), and to optimise the database structure so that the information will be most easily accessed from your future application.

Here are a few simple rules:

• Too many tables are overwhelming, while too few tables make your data structure heavy and reduce its flexibility.
• Instead of storing the same long string in every record of the table (or even a significant part of them), it is better to store that string once in another table, and provide a foreign key to it.
• There is no point in storing the same information several times.
• Think about which data you are going to use, and how.
• It is not really hard to design a good database. All you need is a good book on the subject, a bit of experience, and common sense.

You may find these rules easy and helpful. But, as the time goes by, you are likely to develop your own.

You will see the implications of these rules when reading the next part of the article.

What is SQL?

Structured Query Language (SQL) is a basic tool intended for interaction with any relational database and is supported by all existing engines. It is relatively simple, and its basic statements read just like English sentences. As a result, you can learn the most commonly used SQL statements (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE) within a couple of hours. Of course, that won’t be enough to learn all the possible forms and use cases of those statements, but in most cases it will be enough for you to develop a common database-driven application that will be both flexible and feature rich. (For example, the SELECT statement can do much more than just “SELECT NAME FROM COMPANIES” or even “SELECT EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, COMPANIES.NAME FROM EMPLOYEE INNER JOIN COMPANIES ON EMPLOYEE.COMPANY_ID=COMPANIES.ID”, but it is never too late to go deeper.)

Let’s say we have two tables with data.

COMPANIES:

EMPLOYEES:

(All names are fictitious)

Now let’s see how it is possible to retrieve a subset of rows.

We just run the following query:

SELECT * FROM COMPANIES WHERE TOTAL_SHARES>40000

That is, we are retrieving all the records that match our condition: the total number of shares must exceed 40000. We get:

The result, as we mentioned above, is just another table.

What about the subset of columns?

SELECT ID, NAME, FOUNDATION_DATE FROM COMPANIES

Just another table again.

And, finally, a more complicated example, the above-mentioned

SELECT EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, COMPANIES.NAME FROM EMPLOYEE INNER JOIN COMPANIES ON EMPLOYEE.COMPANY_ID=COMPANIES.ID

The result will be the following:

Now you can see why it is preferable to store company unique IDs (foreign keys) instead of company names, and store names in another table. First, integer IDs are less space-consuming. Second, you can always retrieve names if you know the foreign key values. And what’s the most important is that this structure allows you to fulfill more complicated operations and to develop more flexible applications. If you need to include, say, company owner information together with the company name, in the resulting data set you could just as easily select a statement that allows you to do it using the same ID values. Performance is improved, too.

I’ve made a deliberate mistake in my original database design. Of course, the company owners should be stored in a separate table that would contain their first name in one field, and their last name in another one. That table will store other owners’ details, too. And the only thing needed for the COMPANIES table to store is OWNER_ID, that is, just another foreign key.

This mistake is very typical, so I’ve emphasised it here. Be sure to avoid it whenever you plan on making a new database.

There are lots of different SQL dialects supported by various database engines. In an attempt to standardise the language, an ANSI standard was issued in 1992, which is now named SQL92 or SQL2. There is also an SQL99 specification, but database engine vendors still prefer to enhance the language according to their own conceptions and development plans. So, if you write an application that needs to be compatible ONLY with MSSQL server or ONLY with MySQL, it is enough to learn only the appropriate SQL dialect. But if you are making your application compatible with several or all database engines, make sure all your queries deliver the expected results in all cases.

Database-driven sites and dynamic web applications

What is a database-driven site?

Database-driven (or dynamic) web sites are the opposite of static web sites written in plain HTML and stored as HTML files, which are just plain text files containing special HTML tags. HTML (Hyper Text Markup Language) is a language that allows text formatting and defines how it will look in the browser, but there is not much else that can be done with it.

Things change when an HTML page is not stored as it is, but is dynamically generated using information located in the database and continuously updated. You can go further still: a dynamic web interface running in your web browser allows you to actually change the data in the database, which might be stored on a server located thousands miles away from your desktop computer.

Why would you need a dynamic site?

There are various types of database-driven sites. An E-shop is one of them, and a community portal is another. There are also various online collaboration and online enterprise management solutions, forums, chats (these apps often become parts of portal sites or enterprise applications), and many more. The most remarkable example of a database-driven web application is the search engine.

E-commerce solutions have now become widespread. Presenting goods online, allowing customers to complete their shopping carts and to pay for their goods using a standard gateway such as WorldPay or PayPal – all this is convenient and saves time, so more and more people are seeing the benefits of online shopping. All this requires implementation of a complicated functionality and high reliability, especially when processing payment transactions. So this work is not to be done by amateurs.

However, many ready-made solutions are available for free that facilitate the task of developing an e-shop. Most of the time, the only thing that needs to be done is to customise an existing script to meet the needs of the client. All the commonly used e-commerce functionality is usually already included and is therefore thoroughly tested by the original development team.

So where do the databases come in? The goods, their properties, descriptions, prices, discounts and other information is likely to be stored in one table or several closely related tables. Customers, their personal details, their histories, personal discounts and other things will be in another table (or tables). Databases are capable of storing graphic information, so even the pictures presenting various sorts of goods are here, too.

Database-driven web development

Are you a new developer?

Any new developer who has just begun to work on his or her first dynamic web site will sooner or later face all the common problems that come with it. You might be irritated at first, but if you are ready for such problems, there is nothing to fear.

Here is one typical stumbling block: you are testing your freshly written code and click on the “Refresh” button in your browser. The data already inserted into the database gets added again and you immediately see it via your own web interface. There seems to be no way to get rid of it, since whatever you do, “Refresh” does the same thing again and again: repeats exactly the same request to the server with exactly the same parameters.

Here’s another one: you click the “Back” button and instead of getting to the previously viewed page see a strange “Page has expired” message. Very irritating! And, as in the previous case, you see no way to fix it.

Then there come certain difficulties with the code organisation. Code which is not properly organised on the first stage of development soon becomes hard to support, to debug and, most noticeably, to have new functionality added to it. Good programming style requires separating you dynamically generated HTML pages from the business logic of your application. But what if you are learning web development on your own, and nobody has ever told you how to achieve that separation?

The last task is easily solved by using a framework of your choice. Our PHP Framework is an example of such a script. Having a good PHP expertise helps us to find solutions.

Oh, that “Back” button! disable It? Ignore it?

How do we solve the above-mentioned “Back” button issue? Of course, if the “Back” button is disabled, there will be no problem to worry about.

It is possible to disable the “Back” button, i.e., to make the browser ignore all “Back” button clicks. To achieve this, all the links should be coded not as:

<A href=”http://www.servername.com/index.php”>link text</A>,

but as:

<A href=”javascript:window.location.replace
(‘http://www.servername.com/index.php’)”>link text</A>

Is this the best solution? I think not. It has considerable disadvantages.

First, if the user disables JavaScript execution via the browser settings, then links such as <A href=”javascript:…”> will not work.

Second, JavaScript coded links are not followed by search engine spiders. This is not very important if your application is going to run in a password-protected area, but otherwise it is critical.

And most important: the disabled “Back” button is an example of a very bad usability standard. Browser interface elements are included for users’ convenience, so developers are expected to treat them with due respect. When the “Back” button doesn’t work, it can be even more annoying for a user than the “Page has expired” page.

Many developers just stop at this point and choose not to fix the problem at all. That’s … well … possible. But if you want your application to look respectable and professionally written, I recommend proceeding to the next part and learning how to solve the problem without causing any inconvenience to the user or introducing unnecessary bugs.

Why not handle it gracefully?

There is a way to write your code so that the “Back” button clicks will be processed properly, and the browser interface will work as expected.

When the following solution is used, the server will process requests in a different way. Instead of sending a page to a browser, it sends a response described in http specification. (rfc 2616, paragraph 10.3.4). It tells the browser that a requested page is located at another address, and the browser has to redirect its request there.

For example, the server sends such http response to the requesting browser:

HTTP/1.1 303 See Other
Date: Tue, 10 Jun 2003 11:41:54 GMT
Server: Apache/1.3.27 (Red Hat Linux)
Location: /new_location_for_page.phpThe browser will request the page such as /new_location_for_page.php from the server and display it. It will not consider it a script-generated page and, therefore, it will not show any alerts when a user clicks “Back” to review the page. What’s more, when the “Refresh” button is clicked, only the second GET request is repeated, which is responsible for page visualisation only. The first POST request (which is already handled and all the data updated) doesn’t get “Refreshed”, so the data is not added a second time, and the notification email isn’t sent twice. We are solving two problems at once.

Code samples:

The following PHP function implements the 302 redirect, which is often used instead of 303. Quote from the RFC: Note: Many pre-HTTP/1.1 user agents do not understand the 303 status. When interoperability with such clients is a concern, the 302 status code may be used instead, since most user agents react to a 302 response as described here for 303.

function Redirect ($url) {
Header (“HTTP/1.0 302 Redirect”);
Header (“Location: ” . $url);
exit;
}
?>

Unlike PHP, Java has a ready-made solutions for 302 redirect. The following code is used within our Java framework to redirect all POST requests.
public void service(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
int v = 0;
int nextView=0;
if (req.getParameter(“redir”)!=null) v=Check.getInt(req.getParameter(“redir”));
if (v==0){
//Main handling
nextView=…
}else{
HashMap atts = (HashMap)req.getSession().getAttribute(“atts”+v);
if (atts!=null){
for (Iterator i = atts.keySet().iterator(); i.hasNext(){
String key = (String)i.next();
request.setAttribute(key, atts.get(key));
}
}
}
if (“POST”.equalsIgnoreCase(req.getMethod())){
Enumeration attNames = req.getAttributeNames();
HashMap atts = new HashMap();
while (attNames.hasMoreElements()){
String n = (String) attNames.nextElement();
atts.put(n, req.getAttribute);             }
req.getSession().setAttribute(“atts”+nextView, atts);
res.sendRedirect(WebUtils.encode(“http://”+req.getServerName()
+”:”+req.getServerPort()+req.getRequestURI()+”?”+req.getQueryString()
+”&redir=”+nextView, req, res, true));
}
//Output

sendRedirect is a function in question. It is a standard method belonging to javax.servlet.http.HttpServletResponse class that was written by SUN specialists and, consequently, is thoroughly tested and absolutely safe to use. The rest of the code contains some interesting solutions, too. Feel free to reuse it!

MySQL or PostgreSQL? PHP or Java?

When you are planning on a new database-driven application, it is always necessary to choose the right tool from the start. There is no tool that would fit every case; however, there are several criteria that make it easier to choose one, depending on what kind of application you are going to develop.

First, we have to choose the database engine. There are lots of them currently available, but let us restrict ourselves to just two of them (at least within this article). These are MySQL and PostgreSQL.

Both are free. MySQL is faster, but PostgreSQL is more powerful. MySQL does not need a “vacuum” procedure, but PostgreSQL withstands higher loads. PostgreSQL supports such important features as sub-selects, stored procedures, triggers, unions and views. MySQL doesn’t support all this (the latest versions are supposed to, but they are not yet widely used). What’s more, the MySQL dialect of SQL follows ANSI SQL standards only partly. PostgreSQL is much closer to ideal. But MySQL has the more user-friendly command interface. And it is very popular among web developers and, therefore, thoroughly tested.

For the most simple applications MySQL is, therefore, the best choice, especially if the expected server load is not very high. But as the complexity of future applications increase and the number of potential application users grows, PostgreSQL will more and more become a reasonable choice to consider.

And there’s more: although the novice developer will be quite happy with MySQL, as he or she gains more experience and undertakes more complicated tasks, he or she will long for a more powerful and flexible tool and find that PostgreSQL fits the bill.

In addition, there is the choice of a development tool. As before, we are restricting ourselves to PHP and Java, although there are a lot more.

PHP is a simple tool that has been designed with web development in mind. It is easy to learn, yet it allows a developer to implement various standard web programming tasks, including database-related routines, and offers good flexibility and performance. What’s more, it is free, and is often included in the simplest web hosting packages, even when the hosting is free. It does not require serious effort when administering a web server, either. That makes it the best solution for small businesses, charities, Internet communities and other types of organisations that might not have much money to spare.

On the other hand, when the application in question is large and requires complicated business logic to be implemented, PHP comes with some serious disadvantages, such as an awkward realisation of object-oriented programming, or poorly implemented error handling. It won’t be long before these start to affect the development process. Some of these disadvantages were overcome in the fifth version of the product, but this version is relatively new and not yet widely used.

Java, on the contrary, was not originally written as web language but was successfully adapted to meet web development requirements when HTTP and WWW appeared on the scene. Yet it was object-oriented from the start, not merely enhanced to comply with OOP principles, and it is strongly typed. Strongly-typed languages may be harder to learn and code, but the process of locating and correcting programmers’ errors becomes considerably easier. Java’s strongest point is reliability, which is often worth paying for. For large complicated projects it is definitely a good choice.

Yet to run a web application you always need a web server. And good Java hosting is hard to find and maintain, so comes at a cost. Virtual Java machines are resource-consuming, so servers using them need to be more powerful from the hardware point of view. Additional memory chips, more powerful processors, additional administration efforts … will your client be ready to pay for all this? If yes – then he or she will be rewarded by a stable, feature-rich application, easily upgradable to meet new functionality requirements and showing good performance. But it will be necessary to discuss with the client all the issues beforehand.

Conclusion

The author hopes that you have found this material useful, and thinks the article has covered as many database-related issues as it was possible to do within its limits.

I strongly believe that the Internet is the best place in the world for people to share their knowledge with others, so I will gratefully receive any feedback from you, particularly concerning ways I could improve the article. Also, if there are any topics you’d like to see covered in future articles, or you have any other enquiry, please let us know.

Contact Us