Specifying your requirements
Looking back over past database projects, the one factor which differentiates
the successful from the not-so-successful is whether we were able to define the
requirements accurately at the start. If you have a really clear idea of what
you want your database to do then you are half way to a successful project.
The process of creating a requirements specification is usually a joint
effort between the customer and the database developer, starting with the
customer's view of the database. As the customer, you understand your business
and what you need the database to do. Our job is to translate this information
into a technical specification on which to base the design. It is rare at this
stage to be able to define everything in complete detail, but it is important to
be able to lay down the basics accurately. For example, it is very important to
agree the data types and relationships early on - any change after the project
has started may mean major rework of existing forms, code, queries and reports.
The business context
A business database is usually designed to model some part of the business
process of the company using it. Ideally, a requirements specification should
start with a brief description of the how the business operates, in particular
the part of the business which the database will model. This section does
not need to be long - a paragraph or two is usually enough - but it is
important. It will provide a reality check at each stage of the design:
"Will the database operate like the business? Will it add value to the
process?" A database requires an investment of time and money. It is
important to make sure it is paying back that investment with interest!
It may be tempting to put everything you can think of into a database, but
recognise that you will get the best payback from areas where
- Large quantities of data are involved
- Data is changing, or new data is being generated continually
- Up-to-date data needs to be shared between users
- The data is central to the operation of the company
For example, if your company generates large numbers of quotations and
invoices then they should almost certainly be on a database. But think twice
about including major customer contracts. Even though this data is very
important to the company, if you only handle half a dozen each year then putting
them on a database is probably more trouble than it's worth - it would be easier
to rely on a word-processor and spreadsheets.
Think who the potential users of the system will be and to describe how they
will interact with the system.
- What groups of users will use the system?
- Will they need access to different areas of the system?
- Do they need different levels of access into each part of the system (e.g.
administration, manager, clerical, read-only)
- What data will members of each group input into the system and where will
it come from?
- What information will each type of user need to extract from the system?
- What types of reports will be needed by each type of user?
List the types of data to be stored. The list may include such items as
customer companies and contacts, quotations, advice notes, invoices,
dispatches, supplier companies, supplier products, orders, order items, payments
received, payments made, etc. In the database each type of data would be stored
in a separate table.
Describe as much as you can about the relationships between the types of
data. For example, there would usually be a "one-to-many" relationship
between a company and the company contacts, or between an order and the order
For each table we will need to decide exactly what data "fields"
are to be stored. For example, for each contact we may want to store obvious
things such as last-name, first-name, title, position in company, and less obvious
things such as whether they are a present employee of the company or a past
employee. If these details are not known at the start it is less critical. They
can be filled in later. What is critical is that the tables and their
relationships are understood early on.
The chances are that you will already have a system of some sort in place,
perhaps paper-based or an existing database. If you need to transfer data from a
previous database into the new database then the format of the data and the
process for transferring it will need to be defined. The transfer may be done in
two stages, once at the start of the project (to get realistic quantities of
real data) and once at the end (to transfer across the latest live data). Some
help from the existing database designer may be required.
Existing data may constrain data in the new database. For example, if order
numbers have the form PO/ABC/1234/1999, then the new database may be required to
continue this format.
Database reports may be one of the last things to be considered when writing the
requirements specification, probably because they are standalone objects which
simply extract data from the database and therefore cannot upset its overall
If possible, the requirements specification should describe the data to be
shown in each report, plus the methods available to the user to apply filters
(e.g. to define a date range, or a geographical area). Otherwise, as a minimum,
the specification should say how many reports will be designed in total.
Your database may be simply a repository of data to be entered and retrieved.
However, many databases also process or analyse the data. Access is particularly
strong in this area, with its excellent object-oriented Visual Basic programming
environment. Examples might be:
- Batch updates of all records in a table. For example, to mark all contacts
who have been sent a mail-merge letter
- Calculation of the labour and material costs at
each level in a bill of materials system
- Execution of algorithms for physically locating delegates at a conference
- Complex validation of input data
- How many users in total?
- How many concurrent users?
- The network speed and available bandwidth?
- The minimum specification of the client hardware. e.g. 266Mhz Pentium II
with 64Mb of RAM
- The specification of the server (back-end) hardware
- The likely initial size of the main tables and the growth per annum,
measured in 1000's of records
An operational Access database usually requires less administration
than a client-server system. However, it will require
attention from time to time. You may decide to appoint someone in house to
perform these tasks. It is useful to define up-front how the following will be handled:
- Database backups
- User training and support
- Compact and repair procedures should the database be corrupted
- Installation of the application on new client machines
- Addition and deletion of logins; password reset
- Installation of database upgrades on client machines
- Feedback of error logs and problems via email or phone
The Requirements Specification should detail what will be delivered.
As well as the operational database (front-end and back-end) it may also include a help system,
user documentation, release notes, validation results, source code, training, support,
If there are hard deadlines to be met then these should appear in the
requirements. There may also be times when access to your site, people or
networks is not possible and these should appear as constraints.