Linking a Database to Reach Indexing

Some Concepts… Table Relationships

Event tables are designed to serve as a piece in a relational database. They are the link between a water entity database and NHD (Figure 1). Before you start using your event tables with other data, it is important to consider the relationship of the event table to the data you wish to use with it.

If you are already familiar with Table relationships, click here to skip to the next section.

There are three general types of table relationships:

One to One: When one record in a table relates to only one record in another table and vice versa. A simple example of this would be joining a table containing point discharger information to a table with locational information (Figure 2). Tables with a one to one relationship can be joined based on a common field (in this case Source ID) without any trouble.

One to Many: When one record in one table relates to many records in another. This would be the case if you had a table of EPA Regional Coordinators and another table containing the states in each EPA Region (Figure 3).

You have to be careful how you join tables when you have one to many relationships in ArcView. In the above example, if you join Table A (source) to Table B (destination), the attributes in Table A will append to each appropriate record in Table B. The result would be a table listing the EPA Regional Coordinator associated with each State. (Figure 3A)

However, if you join Table B (source) to Table A (destination), then you will only get one record from Table B appended to Table A and you will lose information. The resulting table will only list one State for each Regional Coordinator.(Figure 3B).

Event tables are always going to act as a "many" table in these relationships because multiple reaches make up the waterbody defined by the Entity ID field.

Many to Many: When many records in one table will relate to many records in another. You can not do a meaningful table join in ArcView under these circumstances, even though ArcView will perform the join.

Always give careful consideration to your input and the output when you begin analyzing your data using table joins.

If you would like to work through an example of linking NHD-RIT event tables to the ADB v.1, download the NHD-RIT tutorial data and work through Lesson 6.

Next: Linking to a database directly from ArcView using ODBC technology.

Figure 1.The relationship between a waterbody database, an event table and the waterbody map displayed using the linkage to NHD.

 Back to text

Figure 2. A one to one relationship is when one record in a table relates to only one record in another table and vice versa.

Back to text

Figure 3. In a one to many relationship, one table can have many records relating to one record in another table.

Back to text

Figure 3A. The result of a one to many table join when performed correctly in ArcView

Back to text

Figure 3B. The result of a one to many table join when performed incorrectly in ArcView 

Back to text