DATABASES

A database is software that enables you to store data, to retrieve it, and to change it when necessary. With the appropriate database this is done easily and efficiently regardless of what the data consists of, or the amount of data you manipulate.

A database is integral to the design, development, and services offered by most e-commerce sites. To allow your customers to search your site for a specific product, you need a database. A database is necessary to collect information about your customers. A mailing list is fed from a database. Another example is that of web publishers who post up-to-the-minute information that is retrieved from a database. Get the picture?

Don’t think you can build an efficient, productive website with its predestined multitude of web pages, without a database. If you try, your site will quickly become unwieldy. Templates, file systems and cut-and-paste can take you only so far. The simplest tasks, such as updating your product catalog, adding editorial content, and even maintaining simple links will eventually overwhelm you.

But take care not to be careless when implementing a database; first draft a strategy for feeding information into your web pages. For instance, look at the informational content of your web pages. Then decide how to implement more efficient ways of managing that content. How is your data currently being stored? What tools are available that can move it to the Web? Also be sure that the database interface is designed so that your employees easily can update the information with only cursory training.

A database can help you create web pages that display every single item in your inventory and keep each and every page current. The easiest way to do this is to create your web pages on-the-fly (or “on demand”) with a program that “queries” a database for inventory items and produces an HTML page based on the results of that query.

That’s just a tip of the iceberg. A database does more than simply provide users with access to information. A database can manage the website, keep links intact, and enhance the security of the site. Furthermore, it’s actually much easier to maintain a database (once it’s up and running) than it is to maintain a static website, with its many individual pages.

Build your e-commerce site around a database. You won’t regret the decision. Now let’s take a closer look at database technology.

Database Management Systems

Technically, a database management system (DBMS) is a software program designed to store and to access information used to support the workings of a website. A database can gather, handle and process information in an organizational structure to facilitate storage and retrieval of information. Once information is entered into a database, a DBMS can manipulate the information so that you can analyze it easily. There are many different types of database structures, but the majority of them organize information in the form of “records” and “columns,” or “entries” and “fields.” On a basic level, a database is somewhat like a set of spreadsheets with rows and columns.

A database will give you the ability to separate your content (your catalog offerings) from your HTML web page (the graphical design). It typically stores your catalog items in separate fields and tables as plain text with no formatting. An HTML template is then designed to provide a structure for the data as it is called from the database so it will be delivered to the website in a consistent layout every time. Databases allow your customers to quickly and to accurately search for what they want since the search is limited to named fields rather than a more expansive, full-text search.

Most brick-and-mortar businesses use databases, so, your IT department should already have experienced database managers. Still, database integration will present special challenges as you join your database with your website. That’s because a website demands that the information fed into its page forms be extracted from the database (or moved from the user into the database) in ways that aren’t necessarily a good fit for most brick-and-mortar relational databases. Also, the rate at which a website’s database receives hits usually far exceeds the norm found in traditional business applications.

Types of Databases

Before we get into the nitty-gritty of how a database models the data, let’s look at the major differences between databases, differentiating between analytical databases and operational databases — since you’ll often hear these terms bandied about whenever databases are discussed.

Analytical Databases: An analytical database, which is also referred to as “On Line Analytical Processing” (OLAP), is a static, read-only database, which stores archived, historical data used for analysis. On the Web, you will often find OLAPs used for inventory catalogs that hold descriptive information about all available products in a business’s inventory. Web pages are generated dynamically by querying the list of available products in the inventory. The end product is a dynamically generated page displaying the requested information pulled from data residing in the database.

Operational Databases: An operational database, which is an integral part of what’s called “On Line Transaction Processing” (OLTP), manages more dynamic data. An OLTP database allows you to do more than simply view archived data — you also can modify that data (add, change, and delete data). Typically, you will find OLTPs used when it is essential to track real-time information, such as the current quantity and availability of an item. As a customer places an order for a product from an online web store, an OLTP keeps track of how many items have been sold and when to reorder the items.

Database Models

A good way to conceptualize a database model is both as the “container” for the data as well as the “methodology” for storing and retrieving the data from the container.

There are basically three database models used today: text file, relational, and object. From the standpoint of simple information retrieval, virtually any kind of information can be plugged into a database (text, images, sound files, movies), but not all databases fit every business situation.

Text File Database

Although it’s debatable whether a text file database is a true database, it is definitely not “relational.” The best attribute of a text file database is its simplicity. Because of their simplicity and ease of use for the novice, you might be tempted to use this type of database for your website, but don’t do it. Text file databases are not scalable and there is generally no concurrent access ability, i.e., two people can’t use the database at the same time. For those with pre-existing text file databases, the most economical approach is to bite the bullet now and move the data to a relational or object-oriented database.

Relational Database

A relational database is a collection of “data items” that are organized as a set of linked tables from which data can be accessed or reassembled in many different ways without need to reorganize the database tables (a table is referred to as a “relation”). This type of database looks like a set of interlocking spreadsheets, with each table in the database being one spreadsheet. Relational databases allow simultaneous updates by numerous individuals. However, there are crucial differences — for example, all the data in a database column must be of the same type and the database rows are not ordered. Some of the best-known relational database packages include Microsoft’s SQL Server, Oracle, Sybase, Informix, and IBM’s DB2.

When designing a relational database, great care is given to “normalize” the structure of the data. The normalization process is performed by applying a series of rules to eliminate redundancy and inconsistency. The columns in all of the tables must depend upon a single key column with values that don’t repeat.

A Relational Database Management System (RDBMS) is a program that enables you to create, to update, and to administer a relational database. Generally you are referring to a RDBMS when you talking about relational databases. An RDBMS typically takes statements written in the extremely popular Structured Query Language (SQL) and creates, updates, or provides access to the relational database. SQL is a “declarative” query language, which means that the user specifies what he or she wants and then the RDBMS query planner figures out how to get it. The RDBMS stores the data in whatever manner it “wishes.”

Figure 17: A typical client/server scenario consists of a user making a request of a database from his/her PC. Client software running on the user’s PC establishes a connection across the LAN or WAN via TCP/IP sockets to another program (the database server) running on a more powerful, centralized PC. Once the connection is verified, the client software sends the SQL queries to the server, which plows through the data and returns the matching data back to the client software, which then displays the records on the user’s PC screen.

Figure 18: For a RDBMS-backed website, the RDBMS client is the web server program (such as Apache) or perhaps a CGI script spawned in response to a user request for a URL. The user types a request or some other text into a form on a web client (Netscape Navigator or Internet Explorer) and that gets sent to the web server, which is itself an RDBMS client, or else spawns an RDBMS client (such as a Perl script) which has or opens a connection to an RDBMS server (such as Oracle). The retrieved data then travels back from the RDBMS server to the RDBMS client, to the web server, which relays it back to the user’s web client.

You need an RDBMS if you have data that changes frequently. Relational databases have concurrency control to ensure that the tables won’t get corrupted even if many people simultaneously write to and read from the database.

The term “client/server” was devised to describe how users work with relational databases.

If you choose to back your website with an RDBMS, make sure your web server software can connect easily and deal efficiently with an RDBMS. The web server software mentioned in this chapter are all multithreaded, Tcl-enabled dynamic web servers designed to handle large scale, dynamic websites, i.e. they can deal efficiently with any RDBMS.

Object-Oriented Database

Generally, when talking about object-oriented databases (ODBMS), most people are in reality talking about an object-oriented database management system. ODBMS is a data management product that is specifically designed for use with an object programming language and/or is closely coupled with one or more object programming languages (C++ or Smalltalk). An object database, therefore, provides database management system functionality to object-oriented programming languages and to the objects that have been created using those programming languages. In theory, this approach unifies both application and database development into one seamless data model and language environment.

Object-oriented databases have long tantalized the business world with the promise of less application code, more natural data modeling, and easier-to-maintain code, but they have taken considerable time to catch on in terms of customer acceptance. While ODBMSs slowly have begun to move into mainstream website development, they still lack scalability and there is limited availability of options to access legacy relational databases. Object-oriented databases may improve both programming time and response time, but you must be willing to go for a whole package — to write in an object-oriented language, to switch to a new database, and to accept the inherent risk of doing things in a new way. But due to two features unique to object-oriented databases — objects can be heterogeneous (each can contain a different collection of “owned” data) and objects can contain some inherent “intelligence” — some e-commerce sites may find that it pays to go the ODBMS route.

Object oriented databases include: GemStone, NeoAccess, ObjectStore, and Fast Objects, to name just a few.

Figure 19: A relational database consists of a set of tables, where each table consists of a fixed collection of fields. An indefinite number of records occurs within each table. However, each record must have a unique primary key, which is a sort of name for that particular bundle of data. Objects in an object-oriented database are bundles of data and behaviors. Because objects in an object-oriented database can contain a variety of attributes and data, queries are often performed through a set of methods. Each object implements these methods in a way that is appropriate for itself. In the example given here, two methods might be “summarize” and “transport.” Graphic courtesy of IBM.

Differences between Relational and Object-Oriented Databases

Relational databases monopolized the business world until the introduction of object-oriented databases. The primary differentiation between relational and object databases is that relational databases are built on a row/column paradigm, similar to a spreadsheet, while object databases treat every item on the website — from graphics, to audio files, to URLs — as separate objects. This differentiation means that rows of flat text map well into a relational database structure, but images and sounds do better in an object-oriented environment.

Relational databases are terrific for storing data that can be converted easily into a two-dimensional representation (i.e. text). However, not all objects found on the Web are amenable to such structuring (i.e. images and sound). Indeed, “flattening out” complex data structures to fit into tables sometimes resembles fitting square pegs into round holes. Thus to use a relational database backend for storing images and sounds, you need special coding that allows the object to be mapped into the entries of the relational database. This extra coding not only puts an added strain on your site’s processing power (speed), it also has the potential to introduce intermittent problems that might have otherwise been avoided.

An RDBMS also uses the intersection of rows and columns for every instance of an entity in the database, regardless of whether a particular cell is needed in a particular instance, while an ODBMS only stores the particular parts of the data actually used in any specific instance.

Unlike a relational DBMS, object DBMSs have no performance overhead to store or retrieve a web of interrelated objects, such as a set of interlocking tables. A natural one-to-one mapping of object programming language objects to database objects allows for higher performance management of objects as well as better management of the complex interrelationships between objects. This makes ODBMSs better suited than RDBMSs to support website document structures, which have complex relationships between data. When everything is working correctly, object databases can operate on complex data, such as images, multimedia, and audio, as efficiently as a relational database operates on simple text or data.

Final notes:

• If you offer images or sounds as a main feature of your website, consider using an object-oriented database.
• Relational databases generally store all of the data on a single server, while object databases can call upon multiple servers.
• It is possible to build a website that uses both relational and object databases in a hybrid system. The relational database can store data such as text and software downloads and the object database can be used for sound, video, or images offered up by the website.

Desktop Database Software

While not a viable solution for most e-commerce businesses, if you are willing to tolerate limited capabilities and performance and are operating on a shoestring budget, you can build a small website using a desktop database software offering. There are three desktop relational database solutions that have some built-in web capabilities, although none come near the quality and performance of a high-end solution. Still, these three products — Microsoft’s Access, Corel’s Paradox, and will, with varying degrees of success, let you get your data up on the Web quickly and inexpensively — as long as you allow for some tweaking here and there.

• Microsoft’s Access allows you to create a database with its Wizards. It is adequate when it’s enveloped by and communicates only with other Microsoft products; but it isn’t happy when forced to interact with other manufacturers’ software.
• Corel’s Paradox allows you to create a database with its “Experts” (which are similar to Microsoft’s Wizards) and like Microsoft’s Access, Paradox too requires much effort to interact with other manufacturers’ software.
• FileMaker Pro is the best of these three products. It is easy to use and has good documentation and templates that will allow you to build basic databases. It is one of the simplest and least expensive relational databases you can buy.

Overview of Robust Database Management Systems

Vendors offer a variety of database management systems that are suitable for e-commerce. Each system has its own strengths, capabilities, and challenges. Here are three of the more popular system:

MS-SQL is a DBMS that is fast, scalable, and free. It offers ease of administration along with numerous automated features (available only for the Windows NT platform) — but there is a trade-off — much of the SQL Server is not user definable with almost everything decided automatically by the system. In cases where the system makes the right choice, this feature works transparently and saves time. However, in cases where the system makes the wrong decision or choice, it can be a nightmare. Following along the automatic configuration idea, Microsoft has tried to create a self-teaching database that examines and adjusts itself to satisfy whatever requirements are being placed on it at a given time.

ObjectStore Enterprise Edition is a reliable OBDMS for high-performance applications built for delivery of high-speed, complex web transactions, and dynamic content needed by today’s web-based businesses. ObjectStore can shorten development time dramatically by reducing the amount of complex code required.

The Oracle DBMS provides a wealth of application possibilities along with increased manageability and improved security. With the JServer option Oracle provides a runtime environment for Java objects and with the WebDB option, Oracle provide the ability to manage the content-creation process while also distributing creation tasks to reduce resource bottlenecks. Oracle addresses the security issue through its “virtual private database” support that allows the attachment of security policies directly to tables and views, thus the same security policy is in place regardless of which application is accessing the data.

Figure 20: All of your website’s content is stored in a database. To “publish” that content on your website, value is added through the use of tools for manipulation, refinement, and presentation. The production of the content is then achieved by selecting, extracting, formatting, and postprocessing the content for specific web pages.