Chemistry Reference
In-Depth Information
many different ways. They are likely to appear in many tables of a chemical
database. Defining a new data type that can store both the concentration
values and its units can be very helpful. It keeps the value and the units
tightly coupled rather than stored in separate columns of a table.
Consider the following table creation for biological data.
Create Table assay1 (id integer, ki float, ki_unit text,
ic50 float, ic50_unit text, ec50 float, ec50_unit text);
It is essential to keep the association of
ki _ unit
with
ki
in order to
accurately express the value. It is also important that units for one data
value are not accidentally associated with those for a different column.
Naming the corresponding columns as above (
ki
and
ki _ unit
,
ec50
and
ec50 _ unit
) helps, but using a composite data type actually enforces
the correct association. This is another example of how database integrity
can be increased. When the conc data type is used, this table becomes:
Create Table assay1 (id integer, ki conc, ic50 conc, ec50 conc);
The units of
ki
are kept associated with the
ki
values inside the
conc
data type.
When using a composite data type, the external representation of the
value is different than the basic SQL data types. The components are rep-
resented as usual for number and text data types, but parentheses are
used to associate the component values. For example, (1.74,nM) is the
external representation of the
conc
value 1.74 nanoMolar. The following
SQL produces sample output for an arbitrary compound id.
Select ic50, ec50 From assay1 Where id = 47665;
ic50 | ec50
-----------+-----------
(12.4,nM) | (1.33,uM)
(10.9,nM) | (2,uM)
(15.,nM) | (1.5,uM)
The individual components of the composite data type are also accessible
using SQL. In this way, the output format can be altered and the individ-
ual components can be used anywhere in an SQL statement. For example,
the following SQL produces sample output as shown below.
Select (ic50).val as "ic50(nM)", (ec50).val||(ec50). unit as ec50
From assay1 where (ic50).val < 10 and (ic50).unit = 'nM';
ic50(nM) | ec50
-----------+-----------
8.5 | 1.23nM
0.7 | 250uM
1.4 | 87uM
Search WWH ::
Custom Search