General Schema Design Decisions

When designing a schema to hold chemical information, it is crucial to first consider how the data will be used. One approach is to interview potential users of the database to determine what questions need to be answered on a regular basis. For example, users of a chemical compound tracking system will typically need to know the following  

It is essential that the schema of tables be created in a way that can easily answer such questions. During the design of a schema, it is useful to prepare structured query language (SQL) queries that will provide the answers to these questions. If it proves difficult or awkward to use the schema to answer these questions, the schema must be redesigned. Do not underestimate the importance of time and effort spent during the design of a database schema. 

Another task in selecting or designing a schema is to determine which operations need to be performed. For example, in a compound-tracking system, compounds need to be registered and samples of compounds need to be taken (checked out). For a schema to function well, it should be relatively simple to update or insert data into the tables of the schema to record these activities. 

It is worthwhile to also consider how important each question or operation is, or perhaps how frequently it will be required. Be sure to determine which operations are essential to the smooth functioning of the system and which questions need to be answered quickly. In other words, prioritize the requirements since it will rarely be possible to satisfy all the requirements without some compromise. Finally, consider questions or operations that might not be needed immediately but will possibly be required in future. 

Of course, it will be important to store the chemical structure itself. Clearly, a complete chemical structure cannot be represented using basic SQL data types, such as numeric or text. While a compound name (text) might be considered a good representation of molecular structure, there are better ways to represent molecular structure. In this chapter, a compound will simply be identified with a unique compound id serving as a foreign key to a more complete representation of structure in another table. The following chapters will show ways that chemical structures can be fully integrated into the tables of a database. This chapter concentrates on the proper use of chemical data and ways in which multiple relational tables can be used. 

---