Washington, September 15-18, 1999 – London, November 21-24, 1999

The Creation of 15 Seconds

Wayne Berry

Introduction

15 Seconds is the largest reference site for IIS and ASP developers. It is a 100% dynamically generated site using Active Server Pages and SQL Server to power its roughly 5000 pages. Once the site is staged, XBuilder is used to create a static snapshot of the site that can be hosted anywhere. This article will be discussing how the site was developed and the speed advantages of hosting the site statically.

Wayne Berry
As a former Microsoft design engineer, and the director of development for FreeShop, Berry's expertise includes software design, development, marketing and online business. The web site he created as a hobby to assist in distributing information to Active Server Page developers became his first product, 15 Seconds. The recent sale of 15 Seconds to Internet.com allowed Berry time to develop his latest product, XBuilder, and Kulshan.com, a community web-site for his hometown, Bellingham, Washington.

Publishing vs. eCommerce

As the Internet develops, there are several distinct types of sites that are appearing from the chaos. I divide them into two sections, eCommerce and publishing. Examples of eCommerce sites would be eBay.com, eToys.com and Amazon.com. Examples of publishing sites would be http://www.microsoft.com/, http://www.msnbc.com/, and http://www.15seconds.com/

From a design perspective, each type of web site is uniquely different. For the most part, eCommerce sites have a section of pages that are different based on the time of day, or the user that is viewing the page. A good example of this is eBay.com, where what is on auction changes based on when particular items go on and off auction. Other eCommerce sites have a large number of pages dedicated to the purchasing process, or pages that up-sell or cross-sell to a particular user.

Publishing sites do not have these individualized pages, in fact the vast majority of their pages do not change with the viewer or the time. For example, out of the five thousand pages that 15 Seconds has there are roughly twenty that change per user interaction including: the searching section, signing up as a consultant, and registering for the newsletter. The techniques that we cover are going to mostly benefit sites that publish, instead of eCommerce sites.

Being a Publisher

I was not really suited for the role of publisher, I had gotten average grades in English class through college and when I started 15 Seconds I had no concept of style, or some of the other basic publishing ideas. However, I did know something about database design and programming. I also knew that I didn't want to format all the articles that I wanted to publish on 15 Seconds. So I designed a database to hold the articles, decided on a format for the articles, a way to list the articles, and designed an ISAPI DLL to run my publishing system. The concepts behind this system for publishing have remained the same since the beginning right up to the present: Separate the content from the format of the content. Though the format of the web site has changed, and the technology has evolved ISAPI to IDC to ASP, this concept has not. In the rest of the article I will discuss how I applied this concept to the development of 15 Seconds.

Database Design

The design concept for the web site was that there were leaf pages that held the information that you wanted and list pages that linked up the leaf pages.

Each row in the table represents the information needed for each leaf page. So, for every row we have a different page. An example of this is the Issue table. This table contains a row for every article that we have published on the site. To add another article you add another row to the table and the information that that row requires, typically the article name, author name, and date of publication.

Each list page links up the leaf pages and provides the user some information about the leaf. This helps the users make an educated decision on which page that they wish to navigate to. In the case of the Issue table, there is a page that lists all issues in order of publication date. This page also contains a short synopsis of the information that the article contains. In Figure 1 there is an example of what the database looks like and in figure 2 there is an example of what the list page looks like:

Figure 1

Figure 2

Once we have the concept of leaf and list down, all we have to do is add extra tables and pages to expand the site. We added a FAQ table for the frequently asked questions. A consultants table for the consultants section, and then design list pages that showed new consultants, and consultants by region. And additional tables for links, knowledge based articles, outside articles, and components. Each time we added a new table, we add a new list page and a new leaf page. As you can imagine the site starts to look like a tree. At the top is the home page with the site-wide navigation, and then each list page is a branch off of the navigation, and then each leaf page is a leaf on that branch.

This is a good straightforward design for a medium size site. However as the site started to grow, the lists became very long. For example, we have over 700 questions and answers in the FAQ section of the site. Which means a list page of the FAQ would be very large and not very helpful. So to solve this problem, we divided the FAQ into categories. Meaning that we would have to have another table called Category and yet anther table called CategoryFAQ that is a many-to-one relationship between the categories and the FAQ. With Categories, we can subdivide the FAQs, making a list page for each category. Each one of the Category list pages for the FAQ would have all the FAQs that falls within that category as defined by the CategoryFAQ table. For example, we have an IIS 1.0 category and an ADSI category, and each category has multiple FAQs about that particular category. Now, when the user goes to the FAQ branch of the web site, they see a list of categories, when choosing the category they go to a list of FAQ in that category and then they can choose the FAQ that they want to read.

We took this concept and applied it to all the different leaf pages in the site. We grouped the articles by category, the Knowledge base articles by category, and the offsite articles by category. We also created a concept called focus sections. Each focus section listed all the articles, offsite articles, knowledge base articles, and FAQ in a particular category, allowing the user to navigate by category, not content type.

This database design is elastic enough to take the site well beyond twenty thousand pages of content, without having to redo the navigation.

Active Server Pages

Once the database design was completed we needed to design the Active Server Pages.

Leaf Pages

The leaf pages consist of a single SQL statement that gets one row from the appropriate table. The primary key to the row is passed in on the page request, so finding the right row is straightforward. The request might look like:

http://dynamic.15Seconds.com/faq/faq.asp?FAQId=1

The ASP code would then look like:

Example 1

nPrimaryKey = Request("FAQId")

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open SQLDSN,SQLUser,SQLPassword

strSQL = "SELECT FAQ_Question, FAQ_Answer FROM FAQ WHERE FAQ_Id = " & nPrimaryKey

Set oRS = oConn.Execute(strSQL)

If (Not oRS.EOF) Then

strQuestion = oRS("FAQ_Question")

strAnswer = oRS("FAQ_Answer")

Else

Response.Redirect("/")

End If

oRS.Close

oConn.Close

<HTML>

</HTML>

Because every FAQ can be seen with a single ASP page, every FAQ is formatted the same. A common problem with sites that are designed by hand, a mistake (or many mistakes) in formatting cause inconsistency with the site.

List Pages

Since each list page for the FAQ is a list of FAQ in a specific Category, the primary Key to the Category has to be passed in to the list page. Here is an example of the request:

http://dynamic.15Seconds.com/faq/list.asp?CategoryId=1

Example 2

nPrimaryKey = Request("CategoryId ")

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open SQLDSN,SQLUser,SQLPassword

strSQL ="SELECT FAQ_Id, FAQ_Question " &_

"FROM FAQ, CategoryFAQ " &_

"WHERE FAQ.FAQ_Id = CategoryFAQ.FAQ_Id AND " &_

" CategoryFAQ.Category_Id = " & nPrimaryKey

Set oRS = oConn.Execute(strSQL)

<HTML>

<BODY>

<UL>

Do While(Not oRS.EOF)

oRS.MoveNext

Loop

oRS.Close

</UL>

</BODY>

oConn.Close

With the list of FAQs dynamically generated, it prevents you from having broken links. Each FAQ must be in the database in order for it to be listed on the site, and because it is in the database it will be displayed by faq.asp, preventing broken links.

From here you need to create a list page, and a leaf page for every type of content that you want to publish.

Leveraging the Database

Once you have the simple pages developed, the minimal number of pages needed to display the information, you can start design pages that take advantage of the database design. For instance:

Ø You can display for the user, every FAQ that is related to the FAQ that they are viewing on the leaf page. By adding another SQL statement to the leaf page, you can display all FAQ that are in the category that the FAQ you are viewing is in.

Ø You can display the next FAQ in the Category and the Previous FAQ in the Category, allowing the user to navigate from leaf node to leaf node without having to go back to the list page.

Ø You can display other content in the same list page, for example if you are looking at all FAQ in the ADSI category you might want to list all articles also.

Ø You can display lists to other content in the lead page. For example, if you are looking at a FAQ in the ADSI category you might want to have a list of Books in that Category also.

Disadvantages

The primary advantage to this type of system is obvious, you can create huge web sites with very little effort. However, there are some disadvantages also.

One disadvantage is that the site tends to be slower then a static site (that consists of .htm files). One of the reasons for this is that when you start leveraging your database, you create more SQL statements per page, and the page response time starts to slow down.

Another disadvantage is that the names of the pages are not user friendly. There is no difference to the user between:

http://dynamic.15Seconds.com/faq/faq.asp?FAQId=2

http://dynamic.15Seconds.com/faq/faq.asp?FAQId=100

http://dynamic.15Seconds.com/faq/list.asp?CategoryId=10

http://dynamic.15Seconds.com/faq/list.asp?CategoryId=1

Another major disadvantage is that some search crawlers do not traverse dynamic sites. This means that your pages will not get into search engines, and you will lose potential visitors to your sites, especially since publishing sites have a lot of keywords that attract visitors to the site.

To solve these problems what you need is static pages, instead of dynamic pages. XBuilder is a tool that creates static pages from dynamic pages, allowing you to stage your site with dynamic .asp pages and serve it with static .htm pages.

XBuilder

15 Seconds uses XBuilder to create static pages from our .asp pages. We started doing this to improve the response time of the site. As the traffic started to grow, our machine started to slow down because of the time it took to generate each page response. What we realized is that our pages didn't change per user, and that most of them remained the same for months on end.

Our problem stemmed from the fact that we were using our machine's CPU power to generate the same page over and over again. We compared it to compiling a file over and over again, and not caching the compiled version of the file. So we created XBuilder to compile our files statically.

We created a separate site that that we called our staging server to hold the ASP files. Or staging server is located at:

http://dynamic.15Seconds.com

This is the site that we work off of, testing development changes and adding new content before it goes live. Once we are ready for the content to go live we use XBuilder to build the site to the directory of the live server at:

http://www.15Seconds.com

Page Naming

The final disadvantage that we had to overcome was the naming of the pages. Because XBuilder can not determine a unique page name for the following two URLs:

http://dynamic.15Seconds.com/faq/faq.asp?FAQId=2

http://dynamic.15Seconds.com/faq/faq.asp?FAQId=100

We have to programmatically name the page for XBuilder. In order to do this we add one line of code to the top of the faq.asp page:

Response.AddHeader "XBuilder-FileName", PrimaryKey & ".htm"

This will create the following page on the static site:

http://dynamic.15Seconds.com/faq/2.htm

http://dynamic.15Seconds.com/faq/100.htm

Now the reader has an appropriate page name for what they are viewing. Another example of naming that page would be for list.asp:

' Some SQL Statement to Get the Category Name From the Category Table Using the Primary Key

Response.AddHeader "XBuilder-FileName", strCategoryName & ".htm"

The XBuilder Bonus

Once we have moved the site over to static pages, there were several bonuses that XBuilder gave us. One of them was the ability to create a CD-ROM of the site that we could sell to improve the revenue. All we had to do was take the dynamic site, and use XBuilder CD Project functionality to build a different set of static files that we could write to a CD-ROM.

Another advantage was that we could host the site on any Internet Service Provider that we wanted. Since the pages generated were static HTML, we no longer need to host the pages on the Internet Information Server, they could be hosted on any server, such as a Unix box. This allowed us to shop around for the cheapest ISP, and it also allowed us to create mirror sites. All we have to do was FTP the static pages to the mirror location and the web server would serve them from that location. Clustering if we needed it would have been easy also.

Summary

It is fairly simple to design a database and some Active Server pages that will allow you to publish a very large publishing site using IIS and SQL Server. Basically, your site is only restricted by your database design. Database sites have the advantage of being easy to maintain, eliminate broken links, and have a consistent format. With Tool like XBuilder you can work around the majority of the disadvantages like: being ignored by the search engine, performance problems, and abstract page names.