Topic DB2: Relationships, Correlation Tables, and Foreign Keys



Relationships, Correlation Tables, and Foreign KeysFrequently, the data that you need to store outgrows a single table. The problem isn’t the number of records—a table can hold any number of rows. But good database design requires that you limit the fields in your tables to only those that directly describe the thing (entity) being stored in the table—generally the entity that the table is named for.For example, in a table of class sections, you might have an instructor field. The instructors have information that describes them: First name, last name, uniqname, phone number, office number. In good database design, you do not put these fields related to the instructors into the sections table. Instead, you create a separate table for instructors which contains the information directly related to them (and not data related directly to the section). The relationship between sections and instructors might be one-to-many, if no section has more than one instructor. However, if any section has more than one instructor, the relationship becomes many-to-many: one section can have many (more than one) instructor, and one instructor can teach many sections. To express a one-to-many relationship, you’ll need two tables: the original sections table and the new instructors table. For a many-to-many relationship, you’ll need three tables: the sections table, the instructors table, and a correlation table which links the two.A relationship in a database is a link between fields in two tables. Typically, the field is part or all of the primary key in one table, while is not part of the key in the other. A field which is a key of one table and used in another (not as part of the key) is referred to as a foreign key in the table where it’s not part of the key. Relationships come in three general types:One-to-one: The first field is uniquely matched to the second, and vice-versa. StudentID and uniqname are a good example. Every StudentID is matched to one and only one uniqname, and each uniqname belongs to one and only one StudentID. One-to-one relationships can be expressed in a single table. One-to-many: One field is uniquely matched to the other, but not vice-versa. Birthday is a good example. Every student has only one birthday, but each birthday applies to many students. One-to-many relationships can sometimes be expressed in a single table, if one of the fields is atomic—that is, it has no more detail we need to know about. A student’s year in school (Freshman, Sophomore, etc.) could be considered to be one of these, if we don’t need to know anything more about them (who’s the class president, for example). If we do need to store detail information about a field like that, we’ll need two tables. Many-to-many: Neither field is uniquely matched to the other. One of the first field can be matched to more than one of the second field, and vice-versa. A student has many classes; a class has many students. Many-to-many relationships require three tables—one for each entity, plus a correlation table which lists all of the links between the two.The diagrams above come from Access’s Relationships tool (ToolsRelationships on the menu in Access 2003). The field names in bold are the primary keys; the lines represent the relationships. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download