Databases are a key backend for many successful online ventures. How do businesses build a framework which will grow with your needs?

To start with the basics, why do Web sites and databases go together in the first place?

It's all about keeping the information fresh and relevant, says Steve McManus, general manager Asia-Pacific for database vendor FileMaker.

"I think the easy thing today is to get a Web site up," McManus says. "The hard thing is keeping it fresh and maintaining it."

Database-connected Web sites should allow the content to be put together by the people who know the most about it. "It's really down to workgroups to drive the content and the information that's on these Web sites. If you've got to introduce another party that's got to translate it and technically do it, it's inefficient and it slows the whole process up. It can also lead to miscommunication. It's better if the people can do it themselves--that way they can do it quicker, faster, and when they want it."

Nevertheless, these Web sites and databases need to fulfil specific and planned business purposes. "When you put a database in, you have to sit back and look at modules. That means looking at your [business] processes and workflows," says McManus. "Those things come before thinking about whether or not you need a Web site. A lot of companies get that wrong."

"I think it's a natural evolution once you've got processes and systems correctly documented: you've gotten rid of duplication and a lot of that happens because you've had to get a systems person in to database your business."

Getting it all connected

This content can then be made available to employees via an intranet; to partners, suppliers, and customers through an extranet; or to customers and the general public over the Internet.

The structure of your current systems has the greatest impact on the architecture required to make these systems interact with the Web, says Ursula Paddon, IBM's market manager for data management. "Where does all the data live that you want to bring to the Web? In what condition is it? What are the performance requirements?"

When choosing architecture, Paddon believes the top three criteria that need to be addressed are:

• Availability;
• Scalability and performance; and
• Flexibility of your database structure.

The most important precautions to ensure availability are well-planned backup and recovery systems, she says. "How can you keep up 24x7, particularly when you want to do trading over the Web? There are many different types of recovery services you can deploy, like split mirror imaging, where you need to copy your whole database into another database so you can switch across at any time and any point of failure."

Because the majority of businesses' back-end systems are heterogenous, bringing them to the Web requires an architecture that can span multiple platforms. "Your transactional data probably lives somewhere in an ERP system or some transactional system, [whereas] your documents or images may live in other systems," Paddon says. "How do you bring this information together in a way that still keeps up the performance for building dynamic Web pages, for example?

"There may be an architectural discussion, but from a database perspective that means you've got to have cross-platform support, you've got to have the ability to move from NT to Unix to Linux to wherever the market takes you."

Another big performance issue is the amount of data that needs to be archived. Legislative requirements add volumes to the amount of data that needs to be stored, says Paddon. "Let's say you have a trading Web site and you have a special offer today and you replace it with another one tomorrow. The legislation requires you to keep track of that. Then there is privacy legislation--you'll have a huge pressure that will force you to have a very structured environment that's also very safe."

"You need to have a different outlook on the database seeding your Web site because it will become far more complex and extended than it used to be. Because you'll be catering to large volumes of data this maintaining and managing it," she says.

Clickstreaming and customisation

"The challenge now for a lot of sites is to provide tailored content to their users, so customers can enter their details and put in a little bit about what they're interested in," says Paul Marriott, business development manager of Oracle 9i, at Oracle. "So you're already managing data so that the next time the user comes back to the site, they can view the details they want."

This customisation goes a step further when Web sites start collecting information about users in order to learn more about them.

"There is this learning process from the data you can collect through the Web site," says IBM's Paddon. This is where we see what was typically used for business intelligence or data warehousing, now you see Web sites are being extended to do this smart data analysis to learn more. That's one way of building smarter and more customer-oriented Web pages."

"Say I'm a bank and I want to make statements available to my customers over the Web. Now [I] have the opportunity in an abstract way to observe customers coming to the Web site. I can observe what else they look at besides their bank statements. Are they looking at home loans or car insurance?"

Paddon says this means businesses can learn more about their customers, which is sometimes referred to as clickstream analysis. "What that means is later I can come back to the site and have the opportunity to proactively market to this person, and for example lead with a home loan offering when this person looks at a statement."

"This means gathering quite a lot of information you don't have in your transaction system. The only thing the transaction system knows about is the last time you visited the ATM. That's a great way of learning about the customers and giving them a better service."

Planning is essential

"It's a huge investment designing and building the database and if a mistake is made when it's designed, it can cost a fortune later on to try and change the structure, migrate all the data, and potentially change any code that's talking to that structure," says Jason Hatch, CTO of Web site developer Presence Online. "So the really important part of all of this is to do the proper business analysis up front and understand what the needs are going to be."

He advises that this should not just be for the next couple of months, but probably the next 12-24 months for most businesses, and even longer with legacy data.

Even if you outsource the development of the database, according to Hatch, having staff with the right skills is still important. "It's really important to make sure that the people you have are skilled enough to keep the database running or the system you have is easy enough for the people you have got." Otherwise, expect to make a lot of phone calls to the consultants who wrote the database. "If you have an ongoing relationship with the consultants that's fine, but it can be expensive in the long run."

There's a strong price-performance trade-off with Web site database development--building the database to perform well takes up expensive development time.

"Making sure there's proper connection pooling, making sure the tables have been created in such a way that there's fast enough access to the data, and that the queries have been optimised. Obviously, doing that takes development time."

Hatch says it also depends on how many hits your site is expected to have and, again, you've got to look ahead. "If you're planning to deal with 100 customers a day that will require a different architecture to 1000 or 10,000 customers a day."

However, you can also run the risk of over-planning, catering to customers you will never serve, and growth that never happens. "If people have a good business plan and they understand what they need and what their growth should be, hopefully they can plan the rest of their business. This includes hardware and the infrastructure around that," says Hatch.

David Pickering, principal software consultant at e-business software developer BMC Software, agrees. "In the old days you used to design an application for a database. You knew that an internal system would have 200 users, so you'd design the application to have 200 users. The challenge is when you put something on the Web you don't know whether it's going to have 200 people or 10,000 people concurrently."

This presents a challenge when coding the database, which can lead to performance issues. "Should they be putting SQL statements into stored procedures and all they do is call a stored procedure, or should they be calling it from the front end and call more data across the network?" Pickering asks.

How it works

It follows a procedure similar to this:

1. The client's Web browser requests a page.
2. The Web server serves a page that contains embedded scripts requesting information from the database.
3. A scripting language interpreter, or an application server, turns these requests into database queries.
4. The application server queries the database--or several databases--and the database returns the results.
5. The results are returned to the Web server, which passes them back to the client's Web browser.

However, the structure of the back-end systems, and the choice of languages used to make the queries, is extremely varied.

The three most common structures are:

• An all-in-one package, such as FileMaker, which allows databases to be published to the Web and acts as a Web server, application server and database.

• Using a Web server with an integrated scripting language--such as using ASP to run Visual Basic scripts in Microsoft IIS Web server, or using Perl or PHP with Apache Web server--which queries a separate database such as MySQL or Microsoft SQL Server.

• A Web server runs scripts to query an application server-that as BEA's WebLogic, IBM's Websphere, or Oracle's 9i application server--which generates a query or list of queries for the database or several databases. This is most frequently done using Java and can involve the use of Enterprise Java Beans.

Which structure or language you choose depends on a variety of factors such as the purpose of the database, the skills your staff have (or have access to), the structure of your existing data and of course your budget.

Keeping it simple

Keeping the structure simple does not mean the database will be simple or inflexible, argues FileMaker's McManus. "If you want to change something on the fly, a field label or the definition of a field, you can do that. With a lot of the competing products, it's like 'you should have really thought about that when you speced out your database, because now you're going to have to start from scratch'."

"When you think about the rate of change in the environment that we're in today, people don't even work in fixed geographies any more--you're in workgroups that come and go, you have people from mixed functional backgrounds, exchanging different types of data. When you've got all those things, you have to have something that's very flexible, robust, and solid."

Two tiers better than one?

BMC Software's David Pickering also believes the three-tier approach is not optimum for performance because it increases the demand for network bandwidth.

"I think most performance issues come back to either poor indexing on the query they're trying to execute, or the fact that they're trying to run SQL statements across the network. They run it from the [front end] program and it's sending a lot of data to the [back end] program, which does manipulations, possibly does two or three SQL statements to get the data back to the end user. The more efficient way is to call a stored procedure, let it do all the processing, and just send the end result back to the application program."

While this runs against current three-tier wisdom, Pickering believes the network is often the bottleneck in application performance. "With a three-tier approach, you move the load across three different tiers and you need to send a lot of data over the network and have to put more load on the network. The more users you add to that application, the more network load you have to have, as well as server bandwidth. If you put most of the processing at the back end, you can have a bigger back end server and not necessarily have to have lot of network bandwidth to process that data."

"The three-tier model has its uses, but I think it's more useful to reduce the network traffic and have the processing at the back end," he says.

Three's a crowd

On the other hand, for large complex sites that deal with a large amount of transactional data, adding a third tier can improve scalability and performance, according to Stefano Picozzi, principal consultant at e-business infrastructure software company BEA Systems.

Using an application server separates the three main activities into three separate layers:

• The application layer--gets the data.

• The business logic layer--transforms data into useful information which is customised for that end user.

• The presentation layer--presents the results to the end user.

"It's that second aspect of the technical programming problem that's addressed by application servers, which is how do I get that data and turn it into something meaningful, or a meaningful e-commerce service?" he says.

Most small to medium businesses use two-tier architectures when they start delivering content over the Web, says Picozzi. "In those, what tends to happen is that the business logic is either tied in the presentation layer or mangled up with the database. That's OK in terms of addressing small-scale requirements."

But as demand for the service grows that puts pressure on the databases in terms of the capability of the extensibility of the system, and the capability of scaling up. "That tends to drive the demand to evolve into a three-tier architecture," says Picozzi. He believes larger organisations have generally already been through this learning curve and may start new Web delivery channels using a three-tier architecture from the outset.

Better infrastructure

"When you're trying to build a Web site and you're trying to access data and present information there's a number of key services you may need, like the ability to reliably execute a transaction, to persist information, to be able to scale, to provide 24x7 reliability, to manage the site, to link your application to some particular security model that you're using."

Picozzi says these are all fundamental infrastructure services that aren't really addressing the business problem itself, but that are needed in providing a commercially viable solution.

Using middleware also provides a pre-built framework and programming model in which to design applications. An application server, "provides standards and guidelines to building the business logic that you need, how you address generating dynamic HTML, and standard APIs for addressing database services," says Picozzi.

By separating the business and presentation logic, the three-tier approach also makes it easier to deliver the same content to different channels, such as Web sites, intranets, and mobile devices. In the two-tier model, particularly because the business and presentation logic often are combined, "this means that re-engineering presentation logic to meet the needs of different client devices carries with it just that much more additional code baggage," says Picozzi. In a three-tier environment, adding new channels is streamlined because, "you only need to redevelop the presentation logic to meet the needs of different device types."

Mind your language

The choice of programming languages--such as ASP, JSP, PHP or Cold Fusion--also depends on the type of site and what it sets out to achieve, says Presence Online's Hatch. "There are fairly big differences, but it's more down to the philosophy behind the design of the language. ASP comes from a basic background and its real goal is to be able to put something together really quickly and to make it functional. It's had a lot of use, so it's quite solid doing that."

"Java and JSP--because of the object-oriented strength of the language--people who come to it from a Java background tend to have a little more discipline about the way things are written. They tend to use good design patterns and three-tiered architectures."

See FileMaker site.

Copyright © 2009 CBS Interactive, a CBS Company. All Rights Reserved.
ZDNET is a registered service mark of CBS Interactive. ZDNET Logo is a service mark of CBS Interactive.