Composite Data Types

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 andecSO 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 cone data type is used, this table becomes ... 

When using a composite data type, the external representation of the value is different than the basic SQL data types. The components are represented 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 cone value 1.74 nanoMolar. The following SQL produces sample output for an arbitrary compound id. 

The individual components of the composite data type are also accessible using SQL. In this way, the output format can be altered and the individual components can be used anywhere in an SQL statement. For example, the following SQL produces sample output as shown below. 

The = is used to denote exact values. As with the cone values described above, keeping the value and the operator together in the same composite data type is preferred over keeping them in separate columns, especially when multiple value-operator pairs exist in the same table. 

For every standard SQL data type available in PostgreSQL, there is a corresponding array data type. While it is possible to define a composite data type for coordinates, consider using the array data type. For example ... 

PostgreSQL is generally referred to as an Object Relational Database Management System (ORDBMS). The use of the word object implies objects in the sense of an object-oriented computer language. While not intended to be fully object-oriented in the same sense as a computer language, an ORDBMS shares the essential aspects of objects. These include composite data types, methods (functions), and inheritance. 

This chapter has shown how composite data types can be of great use in chemical databases. The components of a composite data type are either basic SQL data types, or other composite data types. This is the same way an object is defined in an object-oriented computer language. This level of abstraction can help simplify the development of complex databases. 

The next step is to parse the molfile data into separate columns of the table. The molfile mol function expects a molfile and returns a composite data type named mol. This data type is defined as ... 

Notice that the individual elements of the composite data type value returned by the openbabel.molf ile mol function must be used. Lor example, (openbabel.molfile mol (molf ile)). cansmiles... 

Finally, any properties contained in the molfile will be stored in a separate 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. 

The following SQL is used to parse the molfile column from the vla4.structure table using the openbabel.molfile properties 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. 

While atomic coordinates form the fundamental structure of a molecule, many methods prefer to represent a three-dimensional structure as a surface or a shape. Of course, these are ultimately computed from the atomic coordinates and perhaps atomic partial charges. It may be possible to represent these molecular surfaces or shapes as an array of three-dimensional coordinates. These could be stored as a column in the database analogous to the array of atomic coordinates. It might be necessary to create another data type, perhaps a composite data type, to store molecular surfaces or shapes. Once these representations are stored, they can be used in new SQL functions to assist in searching based on molecular surface or shape. 

---