Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 48

It is essential that the schema of tables be created in a way that can eas-
ily answer such questions. During the design of a schema, it is useful to
prepare structured query language (SQL) queries that will provide the
answers to these questions. If it proves difficult or awkward to use the
schema to answer these questions, the schema must be redesigned. Do not
underestimate the importance of time and effort spent during the design
of a database schema.
Another task in selecting or designing a schema is to determine which
operations need to be performed. For example, in a compound-tracking
system, compounds need to be registered and samples of compounds
need to be taken (checked out). For a schema to function well, it should be
relatively simple to update or insert data into the tables of the schema to
record these activities.
It is worthwhile to also consider how important each question or opera-
tion is, or perhaps how frequently it will be required. Be sure to determine
which operations are essential to the smooth functioning of the system and
which questions need to be answered quickly. In other words, prioritize the
requirements since it will rarely be possible to satisfy all the requirements
without some compromise. Finally, consider questions or operations that
might not be needed immediately but will possibly be required in future.
Once these questions and operations are stated, one needs to consider
the items of information that must be stored in the tables of the schema.
These will typically be words or concepts used in the set of questions posed
during the design of the schema. For example, in a chemical compound-
tracking database, some items would be samples, compounds, chemists,
locations, and checkout and registration events. Sometimes these items are
simple, such as molecular weight or a chemist's name. These can be repre-
sented using simple SQL data types, such as numeric or text. Often, these
items are complex, such as location or sample. These items are represented
by defining a set of columns (a table) that contains simple SQL data types,
or possibly even references to other complex data types. The compound-
tracking example is considered in detail in a later section of this chapter.
Of course, it will be important to store the chemical structure itself.
Clearly, a complete chemical structure cannot be represented using basic
SQL data types, such as numeric or text. While a compound name (text)
might be considered a good representation of molecular structure, there
are better ways to represent molecular structure. In this chapter, a com-
pound will simply be identified with a unique compound id serving as
a foreign key to a more complete representation of structure in another
table. The following chapters will show ways that chemical structures can
be fully integrated into the tables of a database. This chapter concentrates
on the proper use of chemical data and ways in which multiple relational
tables can be used.