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

the same sample _ id may be recorded multiple times in the check-
out table. Each entry with the same sample _ id would have a different
timestamp and likely a different location _ id and chemist _ id . This
provides a record of where this sample has been from the time it was first
registered to the last time it was checked out.
Why have a separate table for chemists, rather than simply storing
chemist _ name in the checkout table? The alternative would be to have
a column for chemist _ name in the checkout table. The advantage of
storing chemist _ name in the checkout table is only that a separate
chemist table is not needed. The disadvantage is that the same chemist
name will be repeated in many rows of the table, possibly spelled differ-
ently or with varying upper and lowercase letters. In addition, the length of
chemist _ name in each row will consist of many characters. The advan-
tages of a separate chemist t able a re ma ny. T The chemist _ id is an integer,
much shorter than a chemist _ name . Each chemist name is stored only
once and can be corrected easily when necessary. Additional information
(additional columns) about each chemist can be added to the chemist table
at any time without disturbing the checkout table. Other tables can be cre-
ated that relate to the chemist table if that becomes necessary. Finally, it is
forbidden to store an invalid chemist _ id in the checkout table because
of the foreign key constraint relating the checkout and chemist tables.
Why have a separate table for alocation related by location _ id
rather than columns location_name and location _ type in the check-
out table? The reasons stated above for using a separate chemist table
apply here, but there are even more important advantages for the alocation
table. While interviewing users of this schema, the location of each sample
was seen to be of high importance. Yet, they were reluctant to define exactly
what was meant by compound location and suggested that it might change
in future. For example, while it might suffice today to know a sample was
in lab 215 or in coldroom 12, in the future it might be necessary to know in
which cabinet or which drawer a compound is located. Keeping the location
information in a separate table allows the definition of location to be altered
or expanded without having to modify the checkout table. A single loca-
tion _ id still relates the checkout table to the alocation table.
The last two tables to consider are the astructure table and the
composition table. Each sample might be composed of multiple chemi-
cal compounds, or might even be of unknown chemical composition. The
astructure table has a unique entry for each compound of interest,
with a unique compound _ id , a SMILES string identifying the structure
of the compound, and a molecular weight. SMILES is a text representa-
tion of chemical structure that is explained more fully in Chapter 7 of
this topic. Other attributes of a chemical compound could also be stored
in the astructure table. It is also possible to create new tables having
compound _ id as a foreign key relating to the astructure table.