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

Figure 11.2 Sample output from phpPgAdmin showing how coordinates and
atom indexes are stored as arrays.
Next, the foreign key constraint is added to the vla4.sdf table. This
ensures relational integrity between the vla4.sdf table and the vla4.
structure table. The following SQL is used.
Alter Table vla4.sdf Add Constraint sdf_id_fk Foreign Key (id)
References vla4.structure (id);
Finally, any properties contained in the molfile will be stored in a sepa-
rate table containing the text value copied from the file as well as a
numeric value for the property, if that is appropriate for the property.
There will be a one-to-many relationship between the structure and
property table, allowing any number of properties to be stored for each
structure. The function openbabel.molfile _ properties is shown
in the Appendix. It expects a molfile and returns a composite data type,
defined as follows.
Create Type openbabel.named_property As (name Text, value Text);
This composite type is created when the openbabel schema and its asso-
ciated functions are created using the code contained in the Appendix. It
is not necessary to issue this SQL command again during the processing
of the data from the vla-4.sdf file.
The following SQL is used to parse the molfile column from the
vla4.structure table using the openbabel.molfile _ proper-
ties function. The name and value fields of the composite data type
are inserted into the vla4.property table, along with the appropriate
id selected from the vla4.structure table along with the molfile
column.