A generic e-commerce template for training via industrial collaboration[1]
Dr Waqar Haque
University of Northern British Columbia
Abstract:
The high cost of turnkey
e-commerce solutions has kept many small businesses out of this lucrative loop.
In this paper, we demonstrate that classroom development of pilot e-commerce
applications can play an important role in the training of students as well as
engaging the local small businesses in this state-of-the-art technology. A
generic web application with sufficient flexibility and functionality has been
developed for a real-world online store. The development has been done in a
manner that it can be used as an effective tool to teach the e-commerce
concepts coupled by simple database design techniques in a classroom
environment. The lab for the course will be supported by and support local
small businesses together with opening many opportunities for students in the
coop stream.
1.
Introduction
The
growth of e-commerce and its impact on supply chain management is an
opportunity for many small businesses. Previously, most large organizations
required that any companies that were part of their supply chains were to be
connected to their Electronic Data Interchange (EDI) systems. However, EDI capabilities
of most small businesses are nonexistent or extremely limited because of lack
of resources to cope with the technological advancements. Many of these
businesses usually run order to order and generally rely on the fax machine
which happens to be the weakest link in their so called automated supply chain.
This often leads to problems that directly affect the labour efficiency and
inventory controls, not to mention the loss of customer base as a result of
dissatisfaction, while the mess is being sorted out.
The
trend toward e-commerce has made it easier for smaller companies to participate
in the supply chain management. Larger companies are now starting to consider
outsourcing supplies and services that traditionally have been sourced
in-house. Small businesses interested in this aspect can take advantage of this
opportunity if they are better equipped with the state-of-the-art information
technology. Similarly, students trained
in this state-of-the-art technology can be very successful in the tightening
labour market that is also becoming increasingly competitive.
In
recent years, UNBC has been approached by a number of small vendors who
desperately wanted to incorporate information technology in their businesses,
but lacked the resources to do so. In addition to the (in-house) skilled labour
shortage, they were also not willing to or could afford to pay for the
exorbitant software licenses particularly considering that their requirements
were extremely conservative. This led our program to take on a pilot study to
determine how we can provide the local small industry with the necessary
support and at the same time train our students in the state-of-the-art
technology to achieve such goals. Thus the goals were two-fold: first, to
develop flexible internet-based applications that translates ordering and
shipping requirements from the clients of a small business accessing the
services through a Web browser; second, to develop the application in a manner
that it could be deconstructed as a teaching tool for an e-commerce course. The
results from this pilot study are very encouraging. We have now developed a
base to achieve both of our goals – the prospects of a self-funded lab
supporting and supported by the local industry as well as training students in
the area of e-commerce using real-world applications and hands-on training.
2. Model and
Design Methodology
The conceptual model of our template is shown in Figure 1. The customers interact with the vendor’s web server through the internet. The web server in turn communicates with the underlying database(s) and presents various forms to the customer to place an order. The web server’s interaction with the database is transparent to the user but is critical to the processing of the order. A key advantage of our solution is that the same application can support a choice of underlying databases.
The design of a front-end, that is web page, was only a secondary requirement of this project and will not be discussed in this paper. However, a simple interface was designed, as it was essential to demonstrate the connectivity and functionality of the template. The emphasis of our work is on the server side programming demonstrating the integration of an application with a choice of underlying database platforms, processing orders and generating reports.
Figure 1. The Conceptual Model
In
order to develop database independent applications, Open Database Connectivity
(ODBC) is a standard method of connecting an application or system to any
underlying database. Most database vendors then provide ODBC drivers as a
method of connecting to and querying their particular database. When a vendor
provides the ODBC driver, the application can use the ODBC Application Program
Interface (API) to connect to that driver. We chose to use a top level API
(PHP’s ODBC function library) to enable easier access to the ODBC API. The only
downside to this could be a loss of response time performance, but that is
negligible for the size of applications that are a focus of this project. The
Data Source Names (DSN) are established to connect the application with the
appropriate database. The location of the odbc drivers and the definition of a
DSN is provided in the odbc initialization files. For example, the following
will create a DSN for MySQL database:
[MySQL_eGrocer]
Description = MySQL Database
Trace = OFF
Driver = MySQL
SERVER = localhost
USER =
PASSWORD =
PORT = 3306
DATABASE = eGrocer
Similarly,
other DSNs are specified in the same file. The application then switches to a
different database by simply switching the DSN. The following code demonstrates
the simplicity and ease of use in switching between two disparate databases
when all drivers are properly installed and DSNs specified:
<?php
// Configuration file for ODBC database connections
putenv( "LD_LIBRARY_PATH=/usr/local/lib" ) ;
putenv( "ODBCINSTINI=/etc/odbcinst.ini" ) ;
putenv( "ODBCINI=/etc/odbc.ini" ) ;
// Uncomment for MySQL
// $dsn = "MySQL_eGrocer" ;
// $user = "egrocer_customer" ;
// $pass = "secretcode" ;
$dsn = "PostgreSQL_eGrocer" ;
$user = "egrocer_customer" ;
$pass = "secretcode" ;
?>
3. Task Breakdown of an Application
The main components of our backend template are the database design, server setup, interface design, business rules and documentation. Each one of these components are briefly described below. A primary goal of this development was to have a final product that is platform-independent and runs on any (popular) operating system that supports a web server, PHP, and an odbc-compliant database.
Figure 2: Task Breakdown
3.1.
Database
The database design includes ER-diagrams, normalization, creating databases and populating tables. This is best done using native relational database languages, such as SQL. We found standard SQL to work in most part and minor format issues, such as DATE representation, were easily resolved. It is also recognized that the database design itself is a very complex process. However, the applications to be undertaken for this course will be small enough[2] which will keep the normalization process very simple. The data to populate the tables will be the real-world data as provided by the vendor. Note that this requires configuring the appropriate SQL servers and setting up user accounts with necessary privileges.
3.2.
Server
The server setup includes initial configuration, setting up the web server and configuring various underlying databases. Our work has the potential to cover both Linux and Windows operating systems. On the linux platforms, we used MySQL, Postgres, and Oracle whereas on the Windows platform we plan to use MS Access, SQL server and MySQL as the underlying databases. An important step in the configuration is to have all odbc drivers installed properly and set up DSNs as described earlier.
3.3. Interface
The interface design includes various types of forms and reports. This design has been kept to a minimal to avoid redirection of the focus of the project. However, it is essential to have functional forms and reports in order to test the server side code that is written using generic odbc function calls. The business rules are specific to an application, but a generic template is currently being developed.
3.4. Documentation
Three different types of documentation must be prepared. The first two are intended for the customer: a user manual to run day-to-day operation and a maintenance guide to add simple functionality without outside help. The third document, reproduction manual, includes step-by-step instructions to reproduce the application and is intended to be used as a classroom reference.
4. Components for
Modular development
We have divided the application development into two major groups: core functionality and optional modules. The reason for this is the limited time available considering this to be a one-term course. In an ideal situation, the course should span at least two semesters, but it imposes many obvious hurdles the most important being the continuity. In any case, the core functionality should be demonstrated upon completion of the course. We would like to emphasize here that most of these components are generally available on most e-commerce sites. However, our goal is to develop these in a generic way keeping the cost to a bare minimum[3] and at the same time provide the necessary training to our students.
Figure 3.
Components for Modular Development
4.1.
Core Modules
The
core functionality of an application would include database connectivity,
product catalogue, virtual shopping cart, an order manager and a customer
manager. All of these should be developed using the generic template making
enhancements as needed.
4.1.1.
Database Connectivity
The
database is central to the core components of the eCommerce application. It is used to store, retrieve and analyze
the data that composes the client's business.
Nearly all modules in the application will directly or indirectly
communicate with the database. The choice of database is, therefore, very
important. We have found MySQL and Postgres to be most user-friendly whereas
Oracle and SQL server are not only expensive, but also comparatively difficult
to configure and communicate with.
4.1.2. Product
Catalogue
Our
template is currently geared towards an online store. In order for customers to
select products for purchase, they must be able to browse a catalogue of the
products available. This catalogue is
organized into categories, is searchable, and contains all relevant information
about the product. When expanded, we plan to include other classes of online
applications such as service industry and Tier 2 suppliers in a supply
management chain.
4.1.3.
Virtual Shopping Cart
The
Shopping Cart module provides the customer with a temporary holding area for
goods they wish to purchase. This will
allow the customer to review the items
they have selected, and to modify their selections before placing an order. The
most challenging part in this development was to retain session information as
a customer navigates the site.
4.1.4.
Order Manager
The
Order Manager takes the information from the shopping cart, references it to
the customer data, and creates an order to be shipped to the customer. It will hold the orders pending management
approval to ensure orders are handled correctly. The processing of orders is
also handled by this module. This allows the vendor to generate meaningful
reports and the end-customer is able to check on the status of an order. The
information is also passed to the Customer Manager for further processing.
4.1.5. Customer
Manager
The
Customer Management module provides an interface to the data relating to the
business' customers. This is where contact and billing information will be
stored. Security should be of primary
concern with this module, and developers should find ways to restrict access to
the associated database tables, taking highly sensitive data, such as credit
card numbers, into account. Note that the secure transaction module will be
developed as an option as described below.
4.2.
Optional Modules
The
optional modules consist of the functionality that can enhance productivity,
but is not essential to the demonstration of the e-commerce capabilities. These
modules range from accounting and payroll modules to customer relation
management (CRM) modules. The modules described below do not form an exhaustive
list, but provides guidance to the customer for potential future enhancements.
It should also be noted that some of these services (for example, secure
transactions) are also provided by 3rd-party vendors and can be
(almost) seamlessly incorporated in most e-commerce solutions.
4.2.1.
Accounting & Payroll
The
Accounting and Payroll module would connect the billing records from the
eCommerce application to the client's existing bookkeeping and accounting
software. This may be necessary in large organizations with many discrete
information systems. This will involve conduit development only and not the
complete rewriting of the accounting/payroll software package.
4.2.2.
Inventory Manager
Our
template currently assumes that no inventory is physically maintained by the
vendor. This is a reasonable assumption for a customer such as an eGrocer, but
not for a parts supplier. Therefore, an Inventory Management module will be
required for such vendors.
This
module will allow tracking of individual items in a vendor's inventory more
precisely. For example, a widget could be traced by its serial number to show
the date it was received from supplier X and the date it was shipped to
customer Y. Similarly, the status of an item (whether in-stock or not and why),
primary supplier, etc can also be determined.
4.2.3.
Stock Re-order
A
Stock Re-order module is a possible extension to the Inventory Management
module described above. This module would interface to a supplier's system to
automatically place orders for items which appear low in the inventory manager. This may not be necessary for smaller
operations, but may be essential as business grows.
4.2.4.
Secure Transactions
By
using secure transactions for sending and receiving sensitive data, you can
guarantee your client, and in return their customers, a higher degree of
privacy when placing orders through your application. An alternative to writing this module is to use 3rd-party
sites for the payment processing part of a transaction. Such services are now
available on a flat-fee or per-transaction fee basis.
4.2.5. Automated
Billing
In
our current template we assume that the payment is collected upon delivery or
all orders are paid in advance. However, this may not apply to all types of
businesses (credit accounts). In those cases an Automated Billing module would
be required. This module will be responsible for sending bills to clients, and
alert the manager to outstanding bills.
It may also interface to customers' accounting systems, bringing the
bills/payments directly into their databases.
4.2.6. 3rd-Party
Interfaces
3rd-Party
Interfaces are any connections/services which are not listed above. These could
include an HR database, a database at the factory manufacturing your products,
secure transactions, etc. In particular, CRM is a rapidly evolving area and a
number of very sophisticated solutions are now available. For small businesses,
a simple order-tracking module may suffice. However, for larger businesses,
securing CRM services through 3rd-party vendors may be more
appropriate.
5. Course
Delivery
We
plan to offer this course at the 3rd/4th-year level in a
12-week semester. The course will consist of two 2-hour lab sessions per week,
for an approximate total of 24 lab sessions. The course will begin by covering
the project requirements, then move on to software installation (including the
OS), databases, application design and implementation and, time permitting, a
partial design/implementation of at least two optional modules. The code will
be developed using odbc and be generic enough to work on at least two
platforms.
It
is realized that this proposed course will need some database background
particularly for the initial db design. Currently, our (only) database course
is taught in the fourth year of the degree program. However, this course is
being revised and will be split into two courses, the first one being offered
in the third year. This database course will become a prerequisite or a
co-requisite for the proposed e-commerce course.
Each
term/semester, a set of 4-6 businesses will be invited to participate in the
course. The students will work in groups of 2-3 students per project. The
businesses will be required to contribute X amount of dollars to the project
which will be disbursed in various ways including coverage of tuition,
membership/subscription to professional organizations and prizes for best
projects. The deliverable from the course will NOT be a turnkey e-commerce
solution. Instead, it will be a pilot project that can be fully developed into
a functional application on-site by a coop student in a subsequent term. In
addition, the requirements analysis and specification will also be kept to a
minimum in order to concentrate on the implementation details[4].
6. Conclusions
The
growth of e-commerce and its impact on supply chain management is an opportunity
for many small businesses. While online
procurement was only 5% of all procurement in Canada last year, a recent study
indicates that this figure will likely increase to 14% this year and to 35% by
2005. In order to exploit this opportunity, we have developed a base to train
our students as well as educate the local small businesses with their support.
The pilot study has demonstrated very encouraging results. The goal is to
extend the scope of the project to a variety of real-world applications in a
self-funded e-commerce lab supported by the local small businesses. The
overwhelming support and interest expressed by local small businesses calls for
offering the course immediately. The initial expected enrollment is 8-12
students working on 4-6 small applications from industrial partners. This
course will also open more Coop opportunities for the students in that stream.
It is important to note that our solution will not require purchase of
expensive licenses and will encourage use of shareware that require none or
very nominal fee for commercial usage.
References
1.
Deepak Thomas and
Devon Dell, Professional PHP 4, Wrox Press, 2002.
2.
Luke Welling and
Laura Thomson, PHP and MySQL Web Development, Sams Publishing, 2001.
3.
Andrew Stopford,
Using PHP with ODBC – A User Guide (http://www.zend.com),
March 2001.
4.
Canadian Business,
Weekly, March 2002.
5.
E-Business
Newsletters, IT World Canada (www.itworldcanada.com)
Name:
Dr. Waqar Haque
Department: Computer Science
Institution: University of Northern British Columbia
Postal
Addr: 3333 University Way, Prince
George, BC V2N 4Z9
e-mail: haque@unbc.ca
