|Creating Database Relationships|
|Part 1: Introduction|
So you've made the move from a spreadsheet to a database. You've set up your tables and painstakingly transferred all of your precious data. You take a well-deserved break, sit back and look at the tables you've created. Wait a second -- they look strangely familiar to the spreadsheets you've just disowned. Did you just reinvent the wheel? What's the difference between a spreadsheet and a database anyway?
One of the major advantages of databases such as Microsoft Access is their ability to maintain relationships between different data tables. The power of a database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table. In this article we'll take a look at the process of creating a simple relationship using a Microsoft Access database.
Imagine a small database we've created for the Acme Widget Company. We want to track both our employees and our customer orders. We might use a table structure similar to the one shown below:
Notice that each order is associated with a specific employee. This information overlap presents the perfect situation for the use of a database relationship. Together we'll create a Foriegn Key relationship that instructs the database that the EmployeeID column in the Orders table corresponds to the EmployeeID column in the Employees table.
Once the relationship is established, we've unleashed a powerful set of features in Microsoft Access. The database will ensure that only values corresponding to a valid employee (as listed in the Employees table) can be inserted in the Orders table. Additionally, we have the option of instructing the database to remove all orders associated with an employee when the employee is deleted from the Employees table.
In the next section of this article, we'll walk through the process of creating the relationship step-by-step with illustrations. Read on!