Chemistry Reference
In-Depth Information
This kind of relationship between two tables is called a one-to-many
relationship because for any one compound in the epa.compound table,
there could be many rows in the logP table, related by the cid column.
Once this type of relationship is established between two tables, it eas-
ily accommodates other tables of data. For example, if several molecular
weight values for each compound were to become necessary, a new table
for molecular weight could be created that would use the same compound
id to relate to the compound table. The column used to relate two tables is
called the key column. In this example, it would be called a primary key
in the epa.compound table and a foreign key in the epa.logP table. Keys
are discussed further in a later section of this chapter.
One-to-many is the most common type of relationship in relational
databases. One-to-one relationships as well as many-to-many relation-
ships are also possible and useful.
2.5.2 One-to-One Relationships
One-to-many relationships are common, but one-to-one relationships are
everywhere. For example, in epa.compound Table 2.2, each compound
has one formula. It would be possible to create a separate table for for-
mulae, in which case there would be a one-to-one relationship between
the epa.compound table and the epa.formula table. There is no funda-
mental need to separate molecular formulae into a separate table, simply
because there is a one-to-one relationship. The relationship is based on
understanding the nature of the data, namely understanding that a com-
pound can have only one molecular formula. Nevertheless, data such as
molecular formulae is sometimes stored in a separate table, for conve-
nience, for clarity, or because it was added at a later date after the table
was constructed.
Before breaking the original table into two tables, each compound
also had just one logP. This was simply because not enough logP data
had been collected or because multiple logP values were not yet of inter-
est. In considering the design of a database schema, it is important to
understand when a one-to-one relationship is an inherent attribute of
the data. Consider the other two columns of the epa.compound table,
molecular weight and melting point. It is possible to have multiple melt-
ing points, say, at different pressures, for each compound. It is also pos-
sible to have different molecular weights for different isotopes of each
compound. If your needs dictate it, you should create a new table for
melting points and molecular weights. If your needs are met by storing
only one melting point and molecular weight per compound, leave these
as columns of the epa.compound table. The rule of thumb is as follows:
If there is a one-to-many relationship between two types of data, store
the data in separate tables.
Search WWH ::
Custom Search