Three-Dimensional Molecular Structure Tables - Design and Use of Relational Databases in Chemistry - page 123

chapter 11
Three-Dimensional Molecular
Structure Tables
11.1 Introduction
In this chapter, various ways are discussed in which tables might be used
to store three-dimensional molecular structures. In these tables, each row
represents a structure. The columns contain molecular properties, which
may consist of arrays of atom properties. In previous chapters, the use of
new data types was introduced to improve the way some data are stored
and searched. Array data types were suggested as a way of storing atomic
coordinates for a molecule. In this chapter, other ways will be shown in
which molecular structures can be stored and searched in a relational
database management system (RDBMS). These include the use of sim-
plified molecular input line entry sytem (SMILES) and entire files from
external sources, such as molfiles or structure data files (SDFs). Methods
are shown to input, output, convert, and search molecular structures from
within the database. The structured query language (SQL) statements
shown are valid in PostgreSQL but may not be valid using other RDBMS.
For example, the array data type is implemented differently in Oracle
compared with PostgreSQL.
11.2 Using Tables Instead of Files
Computer files are routinely used to store chemical information. It might
seem that there is no practical alternative, since computer files are ubiqui-
tous and deeply ingrained into our ways of thinking about computers and
information. A file is an excellent way to temporarily store information
in order to move it from one computer to another, for example, by e-mail,
ftp, or http. Keeping a library of even just hundreds of files of molecu-
lar structures can be inefficient and confusing. Maintaining a schema of
molecular structure tables in an RDBMS is efficient, structured, and reli-
able. Overcoming limitations of using many single files to store molecular
structures have been attempted. For example, an SDF can store multiple
molecular structures using a special symbol ($$$$) to separate structures
within the file. It is also possible to store data about each structure by
adding records to the file. But these additions to the file only increase its
123