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

arbitrary collection of structure ids is often useful. As compounds are
added from an external source, they form such a collection. A chemist
may create other collections for any purpose. The following tables record
lists of registered compounds.

Create Table list_description (id Serial, details Text);
Create Table lists (id Integer References list_description (id),
cid Integer References structure (id));

The list _ description table is updated when a list is created. In
the case of importing compounds from an external source, the list _
description.details column might contain the file name, URL, and
any other information about the source of the compounds. As each com-
pound is processed using the compound _ id function, the compound
id is added to the lists table using the list _ description.id and
the compound id . It is possible for a compound to be contained in the
registry.lists table many times. This reflects the fact that the com-
pound is used in many lists, available from many sources, imported from
many external sources or of interest in many projects. Figure 13.2 shows
an entity relationship diagram incorporating the tables discussed here.
There will be many other tables of experimental data in other schemas
that are related to the registry.structure table.

registry.structure

registry.names

smi
cansmi
grf

TEXT

name TEXT

INTEGER

TEXT

FK id *

TEXT

PK id * INTEGER

fp BIT VARYING

registry.lists
PF id *

registry.list_description
details
PK id *

INTEGER

TEXT
INTEGER

PF id1 * INTEGER

registry.error_log
smi TEXT

attempt TIMESTAMP

Figure 13.2 Entity relationship diagram for schema containing tables for a com-
pound registration system.