With
the obvious exception of a database that
only one person uses, in only one place,
all other database applications have exactly
the same point. In any application where
the same information needs accessing from
two or more places, it is far more convenient
to have all of the applications addressing
the same data store. In a network environment
of course, this dealt with by the file
server.
Online
databases (or web-enabled databases) perform
the same task as a network in that the
data are stored on a data server to which
you can connect from anywhere. This allows
two or more people in different buildings,
different cities or even different countries
to share data. In other words, you no longer
have to live next door to your accountant.
Online
databases also allow different networks
to share information. Universities collaborating
on research projects can use online databases
to allow their networks to ‘meet’ and
therefore share research data.
Because
an online database uses internet technology,
World Wide Web pages can access the data
for 100% up-to-the-minute reports and act
as data collection points with online forms.
This is the perfect medium for research
questionnaires, polls and other data-collections.
In
addition, online databases can interact
with the internet banks and cash-transfer
facilities, allowing smooth, electronic
interaction between your business or project
and your money.
Whether
yours is a small or large business, education
authority, council, university, charity
or association; if you need to access the
same information from several locations,
an online database is most probably your
solution.
How does an Online Database work?
There
are several ‘types’ of online
database. The most simple is a data store
with an internet interface (web site) that
can be accessed anywhere there is an internet
connection by anyone with the right authentication
details.
A
second type is a central online data store
with database applications at each location
needing access.
A
Third type is similar to the second, with
a web interface for reports or data capture
(or both).
A
fourth type is one that is primarily a
data collection point for an online survey
or poll, with either a web interface for
the main data collector, or a database
application front-end.
All
of these have some or all of the following
three elements:
A
data store (the files on the Data Server)
A
web ‘front end’ (a web site)
A
database ‘front-end’ (a proprietary
database [like MS Access®] on a PC or
network)
What
is the Data Server?
All
of the types of online database have some
things in common. Firstly, the data are
stored on a ‘Data Server’ – a
specialist computer with a large storage
area connected to the internet. The data
server does not run any applications other
than those needed to serve data when required
and authorised. The data server may or
may not encrypt and decrypt the data. A
Data Server requires at least one electronic
connection to the ‘outside world’.
Software
called the Database Management System (DBMS)
manages the data on the Data Server. The
DBMS can receive requests (called Queries)
from the outside world. If web page or
database applications call for the data,
they do so using a logic-structured query
language (SQL). There are several ‘flavours’ of
SQL but they all do essentially the same
job and quite often, code written for one
flavour, will work equally well when executed
by another.
What
is a Web Front End?
A
web front end, or internet-based interface,
works in two ways. There are the pages
that deliver formatted results from the
commands sent to the Data Server (for example,
a table showing Orders This Month, or how
many people in the survey were Female).
There are the pages with forms allowing
site visitors to enter and amend data – whether
employees or research participants/investigators,
after an authenticated log in, or members
of the public or registered lists of members
of any group you care to define, with or
without authentication. The web front end
can include online financial transactions.
What
is the Database Application?
Using
a proprietary Relational Database Management
System (RDBMS) which allows the use of
Open Database Connectivity (ODBC) connections
to the Data Server, an application built
to administer the online (and local, offline)
data. This allows office staff (in any
location) to administer the information
in the same manner as using a basic database
application. The database application can
include financial functions.
Using
these three tools, it is possible to create
any form of multi-location database manipulation
system.
How
do I know I really need an online database?
Ask
yourself one simple question – does
it need to be possible for two or more
locations to access your data? If your
answer is ‘yes’, then an online
database solution is an option you need
to consider. There are alternatives. They
usually involve sending data discs around
or fat, attachment-laden emails. Whichever
of these methods is used, it creates copies
of the data, which may quickly become out
of date and confused with the most current
version. Worse, it allows copies of potentially
sensitive (patient records, business details)
to exist outside your control.
How
much does it cost?
Of
course, it will always depend on the amount
of construction work needed, but there
are several elements to consider. Firstly – and
always, the Data Server space needs to
be bought or rented. Space is not in short
supply and is therefore inexpensive. The
main cost the Data Server element brings
to the equation is that of designing, testing
and preparing the data structures that
will hold your information.
Then,
if a Database application is the only connection
to the data, the cost of that construction
will be the next factor. The cost of the
database application is a function of its
sophistication. Applications are costed
according to the time and expertise of
the author(s).
This
is also true of the web front end. As time
is the main element in determining the
final cost of the project, the more basic
the interface, the less time it takes to
develop, the less it costs. There is an
exception to this. You can buy a licence
to use a pre-prepared web template. This
is often a complete site, pre-wriiten,
into which you (or we) paste your content.
These templates can be bought for between
5 and 10% of the cost of having one written.
You will not generally have exclusive
use of this template. It IS possible
to buy an exclusive licence for a site
design template but this of course costs
(about) 10 times the cost of a non-exclusive
site.
What
is the best way of knowing which type
of system I need?
You
know your requirement best. If
you only have a vague idea of what
your system will look-and-feel like,
or if you have a complete picture in
your mind or on paper, you know what
you want it to do. See ‘How
does an Online Database work?’ above.
Knowing what is possible, determine
the best way of achieving yur goal.
Firstly,
you will need a definition of the
purpose of the database. This could
be
a simple sentence along the lines of ‘The
system needs to help me control four
shops’ or ‘The
project requires forms to be filled in
by people on the internet’.
Then design two lists.
The first
is a list of all the queries
and reports you expect to get out of
the data. The
second is a list of
data
items that will need to be stored (employee
and outlet details, or the list of questions
for the questionnaire), in order to
satisfy the first list. See System
Analysis below
for more detail.
The
trick is not to determine what you need today,
but what you will need for the life of
the project. Of the six options, one will
stand out as the solution
to your requirement. If you feel your solution
is not contained in one of these options,
go for the nearest. All options can be tailored.
How
do I perform a System Analysis
There
are two levels of system analysis. The
top level is a definition of what a data
management system needs to do. The second
level is a definition of how the data management
system will do what it needs to do.
In all of the examples below, the first paragraph
is the Top-level analysis and the second is
the (start of) the second level.
Example
1 – Commercial
For
example, let us look at a small business,
employing 10 people, buying a raw material
and producing an end product. Ideally, the
database will store employee details, supplier
and customer details and maintain an audit
trail of stocks of materials and products.
It will also ideally keep track of transactions,
calculate the pay of the employees and issue
wage slips.
The
simplest parts are the static data tables.
For employees, suppliers and customers, three
static data tables are required (it IS possible
to do this with two tables, but three is
generally easier). These all require the
same basic information whilst each has its
own ‘other stuff’. Name, Address,
Telephone, Fax and Email are the common items.
For
Employees, fields for employee number, hours
worked per week, hourly rate, next of kin,
date of birth, holiday entitlement, start
date, end date and reason for finishing,
will be needed. For suppliers, a product
list with current prices, account number
and contact name will be required and for
Customers, account number, discount rating
and contact. If you later want to be able
to determine how long a particular customer
has been buying, a 1st transaction date field
might be useful.
The
example database also needs dynamic tables:
current stock, current salary run and orders.
In addition it will need functions programming
to perform the calculations. It will need
user-friendly forms for data to be added
and amended. Most of all, it will need reports
designing and functions to collect data from
the tables according to criteria, sort it,
arrange it functionally or attractively on
the final print run. It is these report requirements
that determine the final field make-up of
a database design.
Example
2 – Educational
A
university department wishes to commission
a Student/Lecturer/Teaching Session timetabling
system. It needs to be administered via an
Access front end by the secretariat, and
accessed by Students and Lecturers alike
via the internet. Because the output is web-enabled,
the system will require user-authentication
for security
The
system needs four static tables – Students,
Lecturers, Sessions and Locations. The Student
table only requires the student’s name,
email address, course reference and any sub-group
information. The lecturer needs name, department,
email address. A Session record may consist
of Title, time, date and a link to the location
table. On courses with the luxury of non-shared
accommodation, the Location data can be stored
in the Session table.
The
administration of the data is achieved by
storing the data online and using Access
(or other interface) to add, amend and delete
records, assign cohorts to sessions, assign
Lecturers and 'book' locations. The output
can be printed reports from the local application
as well as user-accessible timetables from
the data-served web pages.
Examination
marks and other relevant information can
easily be linked to Students, Lecturers,
courses, sessions or locations.
Example
3 – Catalogues(1) Online Shop
A
toy manufacturer has a range of toys it makes
and sells to the public via the 'normal'
wholesaler > retailer > customer method,
but now wants to try to sell directly to
the public via a web site. The system will
need to store and deliver pictures of products,
along with a description, the cost, possibly
the number in stock (essential if later they
want to sell wholseale on line too) and offer
an online purchasing option. In addition,
the system will store customer email addresses
for future electronic mailshots and newsletters.
This
system requires one static table and one dynamic
one. The static table maintains a complete
list of the
toys,
including
links to picture files. The table is indexed
so searches by category, name or other attribute
can be made by the prospective purchaser.
The dynamic table will store customer email
addresses for email runs. This system will
require accounts to be set up
with credit
card processors
and/or
other
online payment systems (see Absolutely
Everything You Need for Online
Payment Systems).
The
web interface itself needs to allow searches
and other features the customer will find
useful. Two pictures (or one picture dynamically
resized at runtime) will need to be stored
of each toy, to allow the customer to see
a bigger image. The shopping cart feature
and subsequent payment procedures need to
be both secure and simple to use.
Example 4 -
Catalogues(2) Gallery
A
photographer wants to display her work. She
would like her site to attract visitors who
will interact with her and her work, to stay
long enough to discuss a piece, or even just
leave a comment. She wants a special button
on the site so that visitors can see her
photographs with different coloured backgrounds.
She also wants to be able to override that
feature if she decides on a personal preference.
This
application does not need a database as such.
With indexes of filenames, it is possible
to create a completely dynamic gallery site.
The photographer needs to store the data
from her guestbook and her forum however.
She has two options; she could use a ready-made
online chatroom, forum or bulletin Board
system, linking to their servers and data
storage in exchange for some advertising,
or she could download the tools and code-snippets
from enthusiast sites and build one (or she
could get someone to write one for her).
Some web hosts include Chatrooms, forums
and other add-ons as part of their service
(we do).
This
site is a basic gallery site with a few extras
thrown in. A gallery site is effectively
a catalogue site. The difference (in this
case) is that there is no purchase option
and no accompanying database of item details.
Of course a purchase option can always be
added very easily to a gallery site.
Example
4 – Research Projects
A
Post Grad research project requires data input via
a questionnaire. The questionnaire can only be completed
by authorised participants. The resulting data
must be capable of being exported to statistical
analysis software for processing.
This
solution involves an online database to store the
data and a Microsoft Access Interface. It will require
a web interface to present the questionnaire.
Internet-based reports are not required so the
Access database it its only administration area.
This means that Access will be able to provide
the data for the statistical processing software.
Using a secure athorisation system and
email
verification,
the questionnaire
is guranteed to be accessible only to pre-authorised
people. This gurantees the validity
of the data in terms of the identity
of the participant.
