Applications - Design and Use of Relational Databases in Chemistry - page 163

out from within the database. For example, the numeric data type does not
truncate or round the input values and keeps as many significant figures
as there are in the external representation of the number when it is entered.
Using the float or double internal representation will result in slight
rounding of some input values. Computations carried out with float val-
ues are more efficient that computations using numeric values. So, if the
values in the table will be used with many mathematical functions, such as
sqrt , average , standard deviations , etc. and if extreme accuracy is
not crucial, using a float data type may be preferred.
A table of experimental data could be defined as follows.
Create Schema hiv;
Create Table hiv.protease (id Integer References registry.structure,
ic50 Float, ec50 Float, updated Timestamp Default current_timestamp);
This table is intended to hold results of assays testing compounds in reg-
istry.structure for activity as human immunodeficiency virus (HIV) pro-
tease inhibitors. As new assays are added, the test results can be added
to newly created tables with similar definitions. For example, there might
be tables for HIV reverse transcriptase inhibitors stored in a table named
hiv.rt . Other assay results might be stored in new schemas, for example,
fpr.htfc for high-throughput flow cytometry results for the formyl pep-
tide receptor (FPR), or fpr.ca for FPR cell adhesion assay results. Each of
these tables would have columns of data named and typed appropriately
for each assay. Each table would have a column containing a compound
id that references compounds in the registry.structure table.
It is not possible to propose a schema with tables that can accommo-
date experimental results of any type. It is important to consider the needs
of each project and assay so that appropriate tables can be created with
the necessary data types and constraints. One common feature of any
table of experimental data is a column containing a reference to a chemi-
cal compound or compounds involved in the experimental measurement.
While the examples so far have considered only one compound for each
test result row, it is important to consider how results will be handled
when multiple compounds are involved in each experimental measure-
ment, or when multiple measurements are made for the same compound
with samples prepared at different time or perhaps in different ways. A
common way to handle these situations is to use the concept of a sample.
A sample can be defined as a preparation consisting of one or more
compounds and one or more solvents. It might also be necessary to con-
sider separate batches of any compound, obtained at different times from
different vendors or synthesized at different times. Using samples and
sample ids instead of compounds and compound ids is a more accu-
rate reflection of the actual experimental situation and can record more