09-02-2013, 12:53 PM
Database Design for Real-World E-Commerce Systems
Database Design.pdf (Size: 29.33 KB / Downloads: 309)
Abstract
This paper discusses the structure and components of databases for real-world e-commerce systems. We
first present an integrated 8-process value chain needed by the e-commerce system and its associated data
in each stage of the value chain. We then discuss logical components of a typical e-commerce database
system. Finally, we illustrate a detailed design of an e-commerce transaction processing system and
comment on a few design considerations specific to e-commerce database systems, such as the primary
key, foreign key, outer join, use of weak entity, and schema partition. Understanding the structure of ecommerce
database systems will help database designers effectively develop and maintain e-commerce
systems.
Introduction
In this paper, we present the structure and components of databases for real-world e-commerce systems. In general,
an e-commerce system is built by following one of two approaches. The first approach is the customization
approach using a suite of tools such as IBM’s WebSphere Commerce Suite [Shur99]. For example, the Commerce
Suite provides tools for creating the infrastructure of a virtual shopping mall, including catalog templates,
registration, shopping cart, order and payment processing, and a generalized database. The second approach is
the bottom-up development of a system in-house by experts of an individual company. In this case, the developer
is manually building a virtual shopping mall with mix-and-match tools. In addition, a database supporting the
business model of the e-commerce system must be manually developed.
An E-commerce Value Chain and Data Requirements
An e-commerce value chain represents a set of sequenced business processes that show interactions between online
shoppers and e-commerce systems. A value chain helps us understand the business processes of e-commerce
systems and helps identify data requirements for building operational database systems. Treese and Stewart
[TS99] show a four-step value chain that consists of Attract, Interact, Act, and React. Attract gets and keeps
customer interest. Interact turns interest into orders. Act manages orders; React services customers. The fourstep
chain could be considered as a minimal model for a working e-commerce system.
In this paper, we present a more detailed value chain that consists of eight business processes. The new value
chain integrates steps such as personalization, which is usually performed by a separate add-on product. Figure 1
shows the integrated e-commerce value chain with the eight business processes, their goals and data requirements.
High-Level Logical Components
A database schema for a real-world e-commerce system is significantly complicated. Figure 2 shows a package
diagram that shows logical components of a typical e-commerce database. The diagram uses the notation of
Package used in UML [BRJ99]. A package in UML is a construct that groups inter-related modeling elements.
In Figure 2, each package contains one or more related tables.
Schema for E-commerce Transaction Processing Systems (ECTP)
In this section, we present a database schema for an e-commerce transaction processing (ECTP) system that
sells inventory items. Figure 3 shows the ECTP schema and includes some portions from UASP, Order-Invoice-
Payment, Shopping Cart, Inventory, and Delivery packages. Even though a real-world schema would be much
more complicated than the one shown in Figure 3, the schema illustrates a few interesting design considerations
in e-commerce environments. The detailed schema will be different depending on the business rules and models
supported by the site. Note that Figure 3 uses the UML notation. In order to create a relational schema from
Figure 3, we need to add the primary key of the one-side to the many-side as a foreign key.
The proper selection of the primary key is very important. For most tables, we use a system-generated primary
(surrogate) key to avoid dependence on data changes. Smart keys, which have embedded meanings, create
dependency on those data. Thus, changes in those data cause changes in primary keys. All tables in Figure 3 uses
surrogate keys.
Conclusion
In this paper, we presented an e-commerce value chain with eight business processes, logical components of
e-commerce databases, and the schema for an e-commerce transaction processing system. Most real-world ecommerce
database schema will have a similar framework as we presented in this paper. Our experience shows
that e-commerce tools can speed up the development, but still lack certain functionality such as partial orders,
back orders, returns, and email notification to users. Therefore, understanding the structure of e-commerce database
systems will help the database designers effectively develop and maintain the system, regardless of the approach
taken.
From the database design point of view, an interesting research issue is what database structures are needed
to support customization and personalization most effectively. For example, how and what data do we need to
capture to build a web warehouse [KM00] for personalization, and then, how do we communicate with users of
systems? The personalization process requires significant resources to capture clickstream data and user behavior
patterns. Readers are referred to the reference [SL99] for a list of typical OLAP queries and data warehouse
design in e-commerce environments.