Table Relationships in Access
When using Microsoft Access, users will undoubtedly need to understand the basic building blocks of creating a database including tables, keys, and relationships. While creating a data set can be done various ways including creating and importing a spreadsheet into Access, using and understanding the structures of a database can be more complex and determine the application and usability of the database. In fact, some may find it easier to draft table keys and relationships before even entering and editing data contained the tables.
To start, we will review primary and foreign keys and how they determine which data is communicated between tables, then we will later discuss the three types of relationships that are used to govern a database.
- Primary Key - A primary key is necessary in each table as it uniquely defines a specific entry in that table. Commonly used primary keys include student ID, Social Security Number, and Drivers Licence Number. The method of using a number to track an entry is preferable over using names as some names (students, items, prices, etc.) could be similar or the same, which could cause issues in generating reports and queries. In all, each table in a database will always need a primary key in order for it to be part of the database's relationships.
- Example: Consider SJSU Business is considering creating a database of their students and majors. Because some students may have the same name, majors, and/or addresses, using the those as primary keys would not be effective. However students' ID numbers are unique to the individual and thus would serve as the best primary key for that table. So the studentinfo table would contain the student ID as the primary key, along with their name, address, and other contact information.
- Foreign Key - A foreign key is necessary in creating table relationships because it is directly linked to the primary key of another thus creating the relationship between two tables. Unlike primary keys, there can be many foreign keys in a specific table, while only one primary key identifies the table. The foreign key must always be the same type of data as the primary key as this is what establishes the link between tables.
- Example: Consider SJSU Business scenario again, however, Business wants to be able to see student's degree progress. Since we decided our studentinfo table's primary key is student ID, the foreign key of the degreeprogress table would also be the student ID.
Relationships govern how
tables interact with each other and determine the overall structure of
the database. Relationships allow users to link information together
across different tables to produce queries and reports about the data.
The various types of relationships include one-to-many, many-to-many,
and one-to-one; each will be defined and described below.
- One-to-Many - A one-to-many relationship (1:M) describes a relationship between tables where one foreign key is linked to multiple sets of data, while those sets of data are linked to only one unique identifier.
- Example: COMPANY needs to create a relationship between customerinfo and orderinfo. Since each customer can have multiple orders, but orders can only have one customer, the relationship best to be used is the one-to-many using customerID as the primary key in customerinfo and foreign key in orderinfo. This structure assures that each order is unique to one customer, while a customerID would yield multiple orders.
- Example diagram: http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/images/Fig16.gif
- Many-to-Many - Many-to-Many relationships (M:N) are used to link multiple entries in one table to multiple entries in another. Since there is not a M:N option in Access, typically a junction table is required to make this relationship possible on Access.
- Example: SJSU COB wants to create a relationship between studentinfo and advisors to see which students are advised by which adviser. Since students can have a major and a minor, it's possible for students to have many advisers; and since advisers will advise many students, the relationship between the two datasets should be many-to-many. This structure assures that use of the database would yield multiple advisers for each student as well as multiple students for each adviser.
- Example diagram: http://www.techrepublic.com/i/tr/cms/contentPics/associatetables-5285168-e.gif
- One-to-One - One-to-one relationships (1:1) aren't quite as common as the N:M and N:M relationships. They are used to link an entry in one table to the one entry in another table that is unique to that entry.
- Example: Consider SJSU COB wants to create a relationship between graduationform and studentinfo. Since each student can only have one graduation form (at least in this example) and each graduation form can only come from one student, the appropriate relationship between these two tables is one-to-one. This structure assures that a graduation form is linked to only one student, and each student is linked to a unique graduation form.
- Example diagram: http://elearning.ghielaurel.co.cc/wp-content/uploads/2011/04/onetooneRelationships1.gif
These are the basic building blocks of creating relationships among tables in an Access Database. Use of primary and foreign keys establishes the link of information across tables, and relationships govern the overall structure of the database. The appropriate use of keys and relationships will yield efficiently designed databases, and lay the foundation for effective reports and queries.
- Primary Key - http://www.youtube.com/watch?v=h3sYgmcRamQ
- Foreign Key - http://www.youtube.com/watch?v=WqxzwF9GcQg
- 1:M Relationships - http://www.youtube.com/watch?v=qUadUPEjMkc
- M:N Relationships - http://www.youtube.com/watch?v=nszRT3nRUMU