We will be undergoing scheduled maintenance on May 20th, 2013 at 02:00 GMT.
Microsoft Access 2013 Tutorials
Database Design 101 / Understanding Relationships
Subtitles of the Movie (Microsoft Access 2013 / Database Design 101 / Understanding Relationships)
In this short video I'm going to show you a very simple animation and I want to show you the most basic example of a relationship between two tables that I can. This will help you understand not only relationships between tables but why we do these things and it'll also help you understand a little bit about what normalization's all about. Okay. So take a look at this table we have here. Now this table is storing some information about our people. Okay. Notice first name Bob,right? Janet, Alison, Fred and so forth. Then we have an ID column and this is obviously an employee ID for these people. And then we're wanting to record over here the status of their certification. Have they been through the company customer certification course? Accounting certification, HIPAA Certification whatever. Alright. So we just want to know are they certified or not. Well notice the way we've built this table it's telling us that yeah okay Bob is certified but if Janet's not certified then we're storing empty space here. Alison's not certified so empty space. Jack is certified so we got something there. Fred he's not certified so we've got empty space here. Now let's extrapolate this out to the wildest extreme. This table has a 100,000 rows in it and only 10 percent of our people are certified. Then that means that 90 percent of our people are not certified, 90 percent of our 100,000 rows or 90,000 rows are storing blank data. This is not efficient. So what we can do? Well we can remove that column from this table and we can move it into another table. And we're going to take the status column that we had in the previous table. We're going to move it over into it's own table and we're going to copy the ID column. Right. We're just going to put another ID column with the same data type as this one and then we can establish a relationship between these tables. Usually this will be the primary key and this would be called the foreign key. Now notice what this gave us the ability to do. We only store certified data when that particular person gets certified. So we can tell from this ID 33 is certified, that's Bob. ID 37 is certified, that's Jack. So if we want to run a query now instead of in the old way, notice if we go back to here, we'd have had to run a query where you know give us the first name where status equals certified and you know it would had to query every row down through here looking for those things. Right. Well with our new structure, let me get back up here for you, we could run a join query and we say select first name from this table. Then return that first name when the ID of this table matches the ID of this table. And so now it only has to really look at this one to see what IDs are there and match them back and return the names. So it makes it more efficient. It's a little more challenging to write the query but it's much more efficient both in storing it and in querying it. Also if I'm going to make a change to this table at the same time let's say Fred gets a certification. I will go in here add a row, ID 38 certified. Then guess what? You don't, you actually won't hear that sound but Fred is now in our database certified and it's much easier to manage, maintain, it's much more efficient. Okay. So that is a quick little look at relationships, how they work, why we use them and I hope this kind of helps turn the light on for you if you've never been accustomed to seeing this kind of stuff in databases. That's a quick look at relationships.
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
