Join Mark Swift for an in-depth discussion in this video establish relationships, part of Access 2003 Essential Training.
- [Instructor] Next we get to talk about establishing relationships. This is step number four in your seven-step methodology. And this too should be done before you ever touch the keyboard. I've already kind of outlined these first four steps should happen before you touch the computer so you're working your conceptual data. That's the two halves of the design process. One is conceptual where you're modeling your data. And the other design is actual inside the computer where you're relating your tables, and you're building your reports, and you're creating forms for users to enter data.
That should be relatively quick and easy compared to laying down your design and coming up with concept. I'm afraid that a lot of people go to establish their relationships inside Microsoft Access. Access is a nice relationship manager where everything works visually. I myself am going to use it to show you the relationships, but you need to have those relationships firmly mapped out before you touch the keyboard. You need to understand exactly how many of one thing relates to another, or if it's possible for this relationship to be closed or open.
There are four major types of relationships, and we're going to look at those using three different examples. Let's take a look at them. The first type is the one to one relationship. A one to one relationship implies that object A, or a data item in table A, is directly related to a data item in table B, and there can't be more than one. This type of relationship is rare within a database system because if you have one object that can only relate to one other object, and vise versa, then those objects are probably going to be part of the same table.
As a matter of fact, if you find yourself defining objects as one to one more frequently than once in a blue moon, you need to take a look at those objects and make sure that they don't belong inside the same table. An example of an area where a one to one relationship would exist would be, for example, with our sales receipts. Within our receipt system, we have the customer's data all captured in the customer's table. But it might come up that one of our customers, especially a business customer, would have a different address that they want items shipped to.
So we create a table for the alternate address and that alternate address table would be assigned back to one customer and one customer only. And likewise the customer record would only have one alternate address table. So in that case, we have a one to one relationship where one customer can have a maximum of one alternate address table and each address table only has one customer they're related to. But that's one of the few examples in times when you're going to have a true one to one relationship that isn't misplaced.
The next type of relationship is one to many. This relationship describes a single item that could be related to many items, but those many items are all related back to a single record. The relationship is not between the customer and the inventory, instead the relationship exists inside the sales receipt. Since one customer can buy many items and each item could be purchased by many different customers, we can't have a customer to item relationship that exists in a simple way.
And it's inappropriate anyway because the items are being tracked on a sales receipt. To carry on with that line of thought, you would look at each receipt so the line items for each receipt as being a one to many relationship. You have one customer who has many items on the invoice, but the invoice only relates back to a single customer. A single invoice is not going to be attached to multiple customers so that's a one to many relationship. This too is not uncommon, the one to many relationship, but it is definitely lessened than the reverse, the reciprocal, which is a many to one.
The many to one relationship is sometimes referred to as a lookup table where many different a values could be assigned to one item, like the account type within a customer table. The account type could be corporate, government, or personal. Well there are many different things that could be associated to the account type, but the account type can only handle one of those things. An account has to be defined, and that's the lookup table. And it's the same way for instance the charge type. On a sales invoice, you're looking at the charge.
Are they paying by cash? Credit? Check? That category can only be filled by one of those items in our current structure. Potentially the most confusing type of relationship is the many to many. The many to many relationship can't exist in the form you're looking at right now. I've intentionally created this little drawing to show you how absolutely impossible it is to have many items from one table related to many items from another table without some kind of mediator.
In the real world, say for example we were building a database to manage a university. At the university, you're going to have many students taking a class. So the relationship between a class object and a student object is one to many. Well the problem is the class is going to have many students. So where the class has a one to many relationship with students, and the students have a one to many relationship with class because they're going to be taking several classes, that defines a many to many relationship.
I've resolved it for you with my example because the way you handle a many to many relationship is by creating an intersection object, which you can see by the object I've labeled I in the middle. The intersection object then creates two one to many relationships. So for example, in the case of the class to student where you can have many students in each class and each class has many students, the intersecting object might be an enrollment object. The enrollment object then becomes the one to many divider.
You'll have many students enrolled in one class, and you'll also have many classes enrolled for each student. Those two one to many relationships get managed with that intersecting object. In our case, you might look at the example of the sales receipt, and our inventory, and the customers. We already said that we have a many to many relationship between the customers that can buy items and the items that are going to be purchased by customers. The intersecting object in that example is the sales receipt.
We have a customer who is assigned to a single receipt and that receipt then has many items assigned to it. So we have a one to many relationship between customers and receipts because the same customer could come back and buy over and over again meaning that one customer can have many sales receipts. And each single sales receipt is going to have many objects. And in the mix, you've managed a many to many relationship in a kind of an eloquent way that makes sense.
And this is what a finished relationship diagram would look like inside of Microsoft Access. You are going to create something very similar to this once you've mapped out all of your data objects, defined all of your data elements. As you're connecting these tables together, just with a pencil between your hand-drawn objects as you're making your plans, connect them and try to establish relationships. Remember to ask yourself the important questions. Do they have a relationship? And if they do, is it a one to one? One to many? Many to one? Or a many to many relationship? And if it is a many to many relationship, how am I going to manage that relationship? You'll see this relationship diagram again later as we move into Access and actually start making our relationships.