Chemistry Reference
In-Depth Information
2.5.3 Many-to-Many Relationships
In the tables discussed earlier, the experimental or theoretical values were
clearly attributable to one structure or compound. In some cases, say, for
molecular weight, there was a one-to-one relationship with the compound.
In the case of logP shown above, there were many values of logP and the
separate table of logP values exhibited a one-to-many relationship with com-
pounds. Consider the case of compound vendors. There are many vendors
that supply any one compound and each vendor supplies many compounds.
This situation is referred to as a many-to-many relationship between com-
pounds and vendors. A separate table is created to define the unique vendors
and the corresponding vendor ids. Along with the unique compound table,
a third table is defined that contains only compound ids and vendor ids.
The set of rows in this third table with a particular compound id indicates
which vendors supply that compound. And the set of rows with a particular
vendor id indicates which compounds that vendor supplies.
Many-to-many relationships are much easier to understand when
visualization tools are used. Entity relationship diagrams (ERD) help
visualize one-to-one, one-to-many, and many-to-many relationships.
2.6 Entity Relationship Diagrams
The definition of the tables and relationships in a database schema are
completely described using the SQL language. This is discussed in a later
chapter of this topic. Using ERD is an excellent way to create and com-
municate a database schema. These can then be used to write the SQL
necessary to create the tables in the database. There are many software
tools available to create ERD and most of these can automatically output
the proper SQL necessary to create the schema's tables and relationships.
Some software tools even allow reverse engineering of existing schemas
to create ERD. It is a good idea to begin working with ERD as early in the
design process as possible.
The EPA schema described above in epa.compound and epa.logP
can be represented as the ERD shown in Figure 2.1. In this figure, two
tables are represented: epa.compound and epa.logP. The column names
and data types for that column are listed. The PK symbol next to the cid
column in epa.compound denotes that it is a primary key column. The FK
symbol on the cid column of epa.logP denotes that it is a foreign key. The
line joining the two tables shows the one-to-many relationship between
the cid columns. The “crow's feet” symbol on the right side of the line near
the epa.logP table shows that there are (possibly) many entries in the logP
table for each compound, that is, for each unique cid in the epa.compound
table. The circle, or zero symbol near the crow's feet indicates that there
may be zero entries in the logP table for some compounds. The asterisk
Search WWH ::
Custom Search