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

chapter 13
Applications
13.1 Introduction
There are many uses for a chemical relational database. Some of these
have been mentioned in earlier chapters. In this chapter, three general
types of applications will be discussed. The purpose is not to present
complete working applications, but to indicate important issues to con-
sider when designing such applications. Sample schemas are proposed.
The use within each application of the core functions described earlier is
discussed. Each of these applications might be developed as a Web appli-
cation or a client application on a user's desktop. Any computer language
might be used, although the ability to connect to an RDBMS is essential.
13.2 Compound Registration
Every chemical company or research organization has a collection of com-
pounds of interest. These may be compounds synthesized by chemists
employed at the company, compounds purchased from chemical ven-
dors, compounds on which research has been carried out, or any other
collection of compounds. When a new compound becomes of interest, it
is important to know whether that compound has already been entered
into the system, or a new entry needs to be made. The use of canonical
SMILES as a unique name for each structure makes this an easy task.
One essential table in a compound registration system is a table of unique
structures. Such a table could be defined as follows.
Drop Schema registration Cascade;
Create Schema registration;
Set search_path=registration;
-- search_path directs following into the registration schema
Create Table structure (
smi Text Unique Not Null,
cansmi Text Not Null,
id Serial Primary Key,
fp Bit Varying);
Create Function add_new_structure() Returns Trigger As $EOSQL$
Begin
NEW.smi = isosmiles(NEW.smi);
NEW.cansmi = cansmiles(NEW.smi);
155