Designing Geodatabases for Transportation. J. Allison ButlerЧитать онлайн книгу.
There is considerable variety in the nature of primary keys. The duty of a primary key is to uniquely identify each row in a table, which means that there can only be one row with a given primary key value. For this reason, many database designers argue against using a primary key that is entered by the user. This guidance also means the primary key cannot have any implicit meaning other than service as the row identifier. A primary key with intrinsic meaning is called an intelligent key.
Users like intelligent keys because they are easier to remember and they can serve double duty as an attribute. Database designers hate intelligent keys because they are prone to error in data entry and duplication within the database. You may want to use route number, such as SR 98, as the primary key. The problem is that you might accidentally type “RS 98,” or SR 98 might be rerouted, resulting in confusion as to the version a record references. Instead, database designers populate primary keys with integer sequencers supplied by the RDBMS and large, globally unique identifiers created through various mathematical processes. These values are guaranteed to be unique within the table.
All those other potential primary keys—the ones that mean something—are candidate primary keys and, thus, potential foreign keys. They could be primary keys, except for the chance that they might be duplicated within the table, which is the one thing that must never happen to a primary key. Coded values that are used as shorthand for a larger meaning, like a functional class code of 11 that means rural interstate highway, are often candidate primary keys that are chosen to serve as foreign keys. Some foreign keys may also be useful outside the database. These are called public keys, and they include such things as driver’s license number, Social Security number, river-reach code, the three-letter airport abbreviation, the two-letter state and province abbreviation, and highway route number. All of these primary and candidate key concepts are used in this book to demonstrate specific database design solutions. Each has a number of useful applications.
While on the topic of table keys, it is important to acknowledge their two varieties. A simple key consists of a single field. A complex key is composed of more than one field. For a complex key, it is the arrangement of key values that must be unique, not each individual field’s value. Complex keys are useful when a combination of things is required to identify a single member. For example, instead of using a single functional class field to indicate rural/urban location and the type of roadway, you could split them into two fields, one for each aspect of highway functional class. A facility identifier in combination with a date field, such as to indicate the version of SR 98 that opened to traffic in July 2007, is another possible example you may find useful.
The two tables involved in an association relationship are called the origin and the destination. Both contain a field with the same data in the same form, although the number of instances with the same value may differ. The foreign key in the origin table is usually the primary key or a candidate primary key in the destination table. Association relationships are typically described as a ratio of the number of rows that can exist at each end of the relationship. Each number is called a multiplicity and the combination of the two multiplicities is called the relationship’s cardinality.
Multiplicity can be classified as one or many. Thus, cardinality can be the various combinations of these two values: one-to-one (1:1), one-to-many (1:m), and many-to-many (m:n). When the presence of rows at one end of the relationship is optional—in other words, the association doesn’t always happen—multiplicity can be zero, but that does not affect the cardinality. For example, if you designed a rail station database that contained a County table and a Station table, you must allow the number of Station table rows required for a given county to be zero, one, or more. It is, nevertheless, a one-to-many relationship because one county may have zero, one, or more rail stations. The upper bound in the multiplicity determines the cardinality.
In a one-to-one association, each row in one table may be related to one and only one row in the other table. This relationship is relatively rare because putting all the attributes in one table can often eliminate it. However, there are times when it is useful to split attributes of an entity into multiple tables. For instance, there may be a set of attributes that exist for only a small subset of entities or you may want to do different things with each subset of attributes.
The most common cardinality is one to many. In this case, one row in the origin table points to many rows in the destination table. The foreign key goes in the destination table and points to the origin table. For example, if you use coded values in your geodatabase, you will often provide a domain class that lists the range of valid values and ties each value to its meaning. The table with value meanings is the origin and the table where those values are used is the destination. Many rows in the destination table can have the same value, all of which point to one row in the domain (origin) class.
Figure 2.4 The foreign key can go in either or both tables in a one-to-one relationship. Association is a connection that shows which tables participate in the relationship. Multiplicity expresses the cardinality of the relationship; i.e., the number of rows in each table that may participate in the relationship. The foreign key goes in the “many” end of a one-to-many relationship because that is the end with a single possible value. The foreign key in the destination table stores the primary key of the origin table.
The toughest cardinality to address in database design is many to many. This is because you cannot accommodate such an m:n cardinality by simply using a foreign key. A given row in either related table may need to point to an unknown number of rows in the other table, and a column in a relational database can only have one value.
Figure 2.5 Use an associative table to store the many possible relationships and give each one-to-many relationship its own foreign key. In a geodatabase, an associative table is called an attributed relationship class and can include user-defined columns.
To accommodate a many-to-many association, you have to turn the relationship into a table. Such an associative table will contain the primary keys of both related tables. The result is that each end of a many-to-many relationship can be listed in any number of rows in the associative table, thereby resolving the many-to-many relationship as a pair of one-to-many relationships. You can also store other information about the relationship.
Transportation databases are full of many-to-many relationships. For example, a work program project may affect several roads, and a given road may be affected by several projects over several years. Thus, the relationship between roads and projects is many-to-many. You will need a Road-Project table to store the relationship as a set of one-to-many relationships tying each project to one of the facilities it affected. Each row would have one road identifier and one project identifier. A given road or project identifier may occur in the associative table many times, but never more than once in the same combination.
Figure 2.6 Relationships can be given names that express their role. This example shows a one-to-many relationship, where each row in table 1 relates to a collection of rows in table 2. The caret points in the applicable direction along the association. For example, objects in table 2 are part of objects in table 1.
Association relationships are usually obvious in their meaning, but naming relationships can help eliminate ambiguity. For example, you could say that an address includes a street name and that a street name is part of an address, or that an engine is part of a vehicle that may include many other components. We place relationship names next to the association connector and symbolize them with a caret that indicates the direction for which it applies.
Object-relational databases