Notes on Database Design
Overview
This document describes some of the fundamental concepts and procedures
which I have found useful in database design.
Definitions
- database
- Tends to mean one of two things: either just a collection of
data on some physical medium, lying there passively, or
- the data as above (e.g., disk files comprising a MySQL database)
- a protocol for accessing it (e.g., SQL)
- some active agent, like a piece of software, which implements
the protocol (MySQL server + mysql client program for interactive
users, or MySQL server + MySQL client libraries for program access)
Usually we use it in the second sense, which still can be ambiguous.
There may be multiple layers of protocols and associated agents
for a single collection of data. For example, U1 is an additional layer
for the D1 data.
- metadata
- Information about other data such as size, when created,
where to find it, etc.
- bulk data
- The actual stuff users want access to; complement of metadata.
Kinds of requirements
As with requirements on practically anything, database requirements
come in different flavors. Functional requirements
are what the
clients want the thing to do. Interface requirements
(modes of
communication between the client and the facility, requirements on
format of
the information exchanged,..) form a separate but closely-related category.
Performance requirements concern
constraints on resource usage. Each protocol layer has its own set
of functional, interface and performance requirements.
Functional requirements
For any database, at the most abstract level there are just a few things
you can ask it to do:
- Provide read access to data
- Add new data
- Modify existing data
- Delete data
- Monitor itself (keep statistics on usage, log errors, etc.)
Depending on the particulars of the data and the design chosen, it
may make sense to subdivide some of these functions, to cover cases
when
the data in question is bulk data, metadata or both. For example, may
want to support a metadata query of how bulk much data there is
associated with a particular set of cuts, without actually
fetching it. There may also be
global functional requirements, not tied to a particular request,
on database integrity and backup.
Interface requirements
There may be additional requirements on the protocol,
especially the client-side (aka user interface), such as
- support for interactive/human access
- support for batch/program access
- scripting
- "learn" mode where interactive input gets saved to a script
- security requirements
- verification of input
- error reporting
- support (or not) for remote access
Performance and resource requirements
These include such things as
- Limits on resources like disk space, network bandwidth,..
- Required throughput for a single transaction
- Required throughput for series of transactions (limit on
per-transaction overhead)
- Degree of support for concurrency
Determining applicable requirements
The enumerations above can be used as a checklist. For an actual database,
some of these classes of functions and interface considerations
will be important (or, more likely, subsets of some classes will be important)
while others may be uninteresting.
Use cases are a good tool to determine
which are which and to uncover potential performance bottlenecks and
error conditions.
GLAST Software Home
J. Bogart
Last modified: