We will be undergoing scheduled maintenance on May 20th, 2013 at 02:00 GMT.
Microsoft Access 2013 Tutorials
Database Design 101 / Relationship Types pt. 2
Subtitles of the Movie (Microsoft Access 2013 / Database Design 101 / Relationship Types pt. 2)
Well welcome back to Relationship Types. This is Part 2 and I'm just going to continue right where I completed Part 1. We were talking about one to many relationships and so we'll jump right into a diagram of a one to many relationship. Now notice in the first table we have a Student ID column and then we have some people entered here. First name and last name and what we're going to do here is look in a second column. Alright. And I'll have a relationship between these two columns but we're looking at orders here. These are probably book orders or those sorts of things but notice Mary has placed two orders. I can tell that instantly by looking because Student ID 1 there's one record in this table for Mary Smith but notice there are two records in this table. So there is a one to many relationship. And there could be three, four, five, ten twenty, who knows. Okay. But it's based on this student ID. Now this prevents us from having Order ID 1, Order ID 2, Order ID 3 and so forth in this table. And so normalization has helped us but that also makes it very efficient for storing this data only for the people who have placed orders. Notice Tom James hasn't ordered yet, he's not in this other table. Alice Jones has ordered one item or she's had, placed one order. And so this gives you a little simple look at what a one to many relationship looks like. And again this is the most common type that you'll see in database designs. We basically continue to create more and more one to many relationships in our tables. And this is really what normalization becomes is a bunch of one to many relationships that just drill deeper and deeper and deeper into the data. Now the last one that's kind of bizarre is a many to many relationship. And this is where a record in either table can be related to zero, one or many records in the other table. Now table 1 contains students, table 2 contains clubs. So a student can be in multiple clubs and a club can have multiple students. So how do we represent that in a relationship type environment? And the simple answer is can't be modeled in a relational database. Okay. It can't be modeled between just two tables but it can be modeled in a relational database using a third table which is called a linking table that kind of mimics the relationship. Now if this one doesn't make sense at first don't worry about it, just look at it, kind of dig into it, concentrate on it a little bit and it'll pop in later. Notice here are our students. Right. Same people Mary, Tom and Alice, one, two, three on the student IDs and skip the middle table for a second and notice the, what we would normally have is a second table over here is our club. Notice these are our clubs science, math and art for right now and we have assigned a club ID. Well how do we determine which students are parts of which club and which club belongs to which student? The answer is this linking table. We put an SID in here and we put a CID in here. So notice Mary is a member of the math club Mary is a member of the art club. Now the art club has Mary as a member and the art club has Alice a member. So this linking table makes the magic happen to determine that many of these here can be related to these people. And many of these here can be related to these clubs. I hope that makes sense to you. Now this is what the relationships look like on this and you can see we're connecting the student table to the linking table via SID. And obviously we're connecting the linking table to the club table via CID and by comparing these we can determine who's on either side. Now you'll still have to write some queries here that can get kind of interesting. But this is the visual concept of a many to many relationship. Now I hope these two videos help you kind of get your head around this idea of relationships and types. And when you read about these out there in the books about database design, now you have an idea of what they're talking about. Don't let them intimidate you, don't let them confuse you. You will lose focus on this. I always tell my students it's kind of like when you first turn on a fluorescent light, it kind of flashes for a few minutes and the flashes get faster and then the light comes on. It's kind of the way you'll do on this stuff too. Okay. So that's a quick look at relationship types.
Tutorial Information
| Course: | Microsoft Access 2013 |
| Author: | Mark Long |
| SKU: | 34405 |
| ISBN: | 978-1-61866-090-9 |
| Release Date: | 2013-02-01 |
| Duration: | 7 hrs / 89 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
