Welcome to the fifth instalment in this Visual Basic Database tutorial, exclusively hosted on VB-World.
As ever, I’m your amazingly cool host Karl Moore. In fact, as we’re getting to know each other a little more each week, I’ll let you call me Karlos. May I call you "reader"?
Don’t forget that I love to hear your feedback. If you’re enjoying the tutorial, then I want to know! My e-mail address is Karl@karlmoore.com
And if you’re not enjoying the tutorial, the Editor wants to know. Hah, like I’m going to give you his e-mail address?
[Ed: It’s firstname.lastname@example.org]
Just don’t tell him you don’t like my humour. Otherwise I’ll be out of a job, with three iguanas and a mangy looking cow in its late 40s to support.
Anyway, enough pitter-patter. On with the content! This week we’ll be taking a look at:
- How best to design a database
- Creating a table
- Creating a chair
- Primary keys
- Foreign keys
- House keys
- Relationships (Dr Ruth, anyone?)
So onwards, good sir… let’s get down and dirty on the disco floor with the database diva… (that’s apparently me, by the way).
Let’s face it. There are three types of people in this life; the good, the bad and the ugly.
You and I fall into the first bracket; the stunningly good looking and wonderfully intelligent group. The Editor falls into the second band. And the average database design falls into the last, rather disgusting category.
This week, I’d like to give your database design a makeover and move it straight into the upper echelons of the "Good".
So lets start with a case study. Sit back and imagine we’re running a pet hospital. You need to record all the information about me. And my pet iguanas. And my pet cow.
Based on the information I’ve given you already, you’d probably store my name and address all on one row. And on that same line, you’d add details of my pet. Your basic table might look like this:
|121||Karl Moore||The Infirmary||Wiggles||Iguana|
But what about my other pet iguana, Green Thing?
Hmm, I guess you could change your table to look like this:
|121||Karl Moore||The Infirmary||Wiggles||Iguana||Green Thing||Iguana|
That’s brilliant. Well, in a kind of non-brilliant way.
You see, you’ve forgotten my third iguana, Strangely Brown. Oh dear.
So how do you get around this? Sure, you could add a third column. But then you’ve forgotten about my pet cow, Daisy. Doh!
"So what should you do?", I hear you cry. Enter stage a secondary table.
Let me explain; imagine having one table holding all my basic information like this:
|121||Karl Moore||The Infirmary|
And then a secondary table holding information about each of my pets with the details of each animal on a new line like this:
That’s great, but what links Wiggles the iguana to its owner, me, Karl Moore?
You probably noticed my customer ID number in the first table. And likewise, the second table also holds an ID number
So any pets in the second table with the ID number of 121 belong to the owner with an ID number of 121 also. Simple, eh?
Top Tip: No self-respecting geek calls those matching values, err, matching values. Instead the ID field in the main table is known as the Primary Key and the ID field in the second table is known as the Foreign Key.
And a good database design really isn’t awfully difficult to program around. Let’s say you allow your user to browse through the main list of owners and have a list of pet names for that owner displayed in a DataGrid at the bottom of your screen.
You’d simply tell your program, in geeky programming terms: "Every time the user moves a record in the main customer table, display all the details from the pets table where the ID number in the pets table is the same as the current customer’s ID number".
OK, so it might sound a teensy-weeeensy bit complicated. But it ain’t. Really.
At least, not for mega supercool geeks like you and I…
So, in summary, it’s good practice to throw anything you may need to store multiple values for, in a separate table. You can then link that information back to its ‘owner’ via a ‘key’, which is typically some form of ID number.
So let’s put this theory into practice. Start Microsoft Access and create a new database say, c:surgery.mdb
Click on the Tables tab, hit the New button, select Design View and click OK.
You should be presented with the table design window, which allows you to define the fields you want in your table. It should look a little like this at the moment:
Your cursor should be flashing in the first ‘Field Name’ box at the moment. We’ll call this field ‘OwnerID’ so type it in and press Tab.
You should now be in the ‘Data Type’ box. This is where you tell Access what type of information you want the field to hold. For example, you could tell it to hold normal Text, or perhaps a Date, or perhaps a Number, or perhaps… an AutoNumber.
Select the latter. An AutoNumber is an automatically generated number that will be inserted into the field each time a new record is entered into the table. And it’s completely unique, too which is just what we want for the OwnerID field.
Now this field will be the ‘Primary Key’ in other words, that main number which uniquely identifies an owner. And to make sure Access knows this, click on the ‘Primary Key’ button on your toolbar. You should see a small key appear beside the Field Name.
Click down into the next ‘Field Name’ box and enter ‘OwnerName’. Tab across and select the ‘Text’ data type. The bottom half of your screen should look like this:
This lists all the properties of that one field. You might, for example, change the ‘Default Value’ property to ‘John’ meaning that ‘John’ will be automatically inserted into that field as a default name every time you add a record.
Or you may change the ‘Field Size’. At the moment, our field will only hold up to 50 characters. But we’re going to change that to allow for owners with particularly long names go on, up the value to 100 characters!
Also, change the ‘Required’ property to Yes. That means a user cannot add a new record to the table without this field OwnerName being completed.
Add another Field Name and call it ‘Address’. Again, change the Field Size to 100 and the Required property to Yes.
Your screen should look like this right now:
Click File and Save. Enter the name "Owners" and click OK. Now close the table and follow the exact same process to create a table with the following specifications:
- Table Name – Pets
- 1st Field Name – OwnerID
- 1st Field Data Type – Number
- 2nd Field Name – PetName
- 2nd Field Data Type – Text
- 3rd Field Name – Breed
- 3rd Field Data Type – Text
When saving the table, you may get prompted to add a Primary Key just click No. This is the secondary table, which contains the Foreign Key (OrderID) we mentioned earlier.
We’ve almost completed the database design; we’ve created two tables, with an AutoNumber in one and a number field for that AutoNumber in the second. All that remains is for us to tell Access that there is a relationship between the first Owners table and the secondary Pets table.
Return to the main Database window and click the Relationships button on the toolbar.
You’ll be prompted with a box asking which tables you wish to show in your Relationships diagram. Double-click on both Owners and Pets, then click Close. Your screen should look something like this:
At this point, we want to tell Access there is a relationship between the Owners table and Pets table. Drag the OwnerID field in the Owners table over to the OwnerID field in the Pets table and let go of the mouse button.
You should be prompted with the following:
Notice that Access has determined the relationship type as being ‘One-To-Many’ meaning there will be one occurrence of the OwnerID number in the first Owners table and possibly numerous instances of that number in the Pets table.
And that’s right one owner can have multiple pets. Just as one company department may have many employees. Just as each customer order may have numerous individual order items.
So this One-To-Many relationship isn’t justuseful in the vetinary world.
Check the ‘Enforce Referential Integrity’ button; this will ensure your data stays in tip-top condition. In other words, your users won’t be allowed to enter a value in the OwnerID field of the Pets table that doesn’t exist in the Owners table. After all, you can’t really associate a pet with an owner that doesn’t exist!
All the referential integrity thing does is enforce that rule.
Click OK. Your screen should look something like this:
Click on File, Save to store the relationship.
And that’s it… you’ve completed your database design!
Now if you wanted to be a real boffin, you could probably put all the breeds in a separate table and instead of inserting a text value in the Breed field insert a number, a Foreign Key linking it back to a Breed description in another table. Don’t forget that storing a number is much more efficient than storing a piece of text. And it’s certainly less prone to typis.
[Ed: Err… Typos]
But we’ll leave that till another day. For now, give yourself a bally hard pat on the back… you’ve completed the database design! And it’s absolutely mega-cool! Hah, move over Bill Gates…
Don’t know about you, but most of my programming never works first time. In fact, most of it just never works. But I’ve a good feeling about this database design… and it’s not just ’cause I spent hours testing it.
So let’s put our database design through the works. First off, let’s enter a new owner into the Owners table.
Open it up and throw in a little sample information. Here’s mine:
Note that I’ve been automatically assigned a unique OwnerID of 1.
BRILLIANT! That’s our Primary Key stepping in.
Now open up our Pets table and enter a little more sample information. But this time, enter number 15 in the OwnerID field. In other words, you’re saying this pet belongs to the non-existent owner number 15.
When you try to move off the record, you should receive the following groan:
Or in other words, "You can’t add a pet without it having an owner, you crazy crazy crazy person!"
Try changing the OwnerID to 1 and adding the pet once more.
Does it work? Bravo!!
Try adding all my pets to your list; the three iguanas Wiggles, Green Thing and Strangely Brown, and that 48 year old cow, Daisy.
It’s worth noting that you won’t be able to put anything but a number in the OwnerID field. When we designed the table, we choose the ‘Number’ data type which protects it from strange dates, bits of horrid text or weird boolean values. Groovy!
OK go back to the Owners table and try to add another record. You’ll notice the OwnerID number is automatically incremented…
… and you should now be allowed to add entries to the Pets table using that newly-generated OwnerID number.
Hurrah! Go on… shout hurrah!!
Wow, we sure covered a lot of ground today!
I babbled about good database design for a while, and then we delved into Access to create our very own tables complete with keys and relationships. Finally, we tested our supercool design… and it worked!
Next week we’ll be taking this project further. I’ll show you how to easily build your Visual Basic application around a good database design… using a few little-known control properties.
Tune in at the same time next week for more of the same. Until then, I’m your host Karl Moore saying goodnight for tonight… goodnight!
[Ed: You can download the database Karl has been working on this week here]
Karl’s Database Tutorial Index
Visual Basic Database Tutorial – Part 1
What do VB-World.net and databases have in common? Well for a start, the letters "b", "e" and "t". But more relevantly, over the next couple of months, it’ll be hosting a weekly Visual Basic database tutorial with head geek, Karl Moore.
Visual Basic Database Tutorial – Part 2
Wondering how to use databases in Visual Basic? Ponder no more. This week, head geek Karl Moore returns with the second part in his Visual Basic Database tutorial. That’s right, he’s back… and he’s geekier than ever.
Visual Basic Database Tutorial – Part 3
The Head Geek is back with the third part in his Visual Basic Database tutorial. Join Karl Moore as he takes you on a wobbly ride through the wacky world of Access, queries, grids and controls. And that’s just this week!
Visual Basic Database Tutorial – Part 4
Head geek Karl Moore returns with another extra helping of juicy database info. This week, Karl explains how to access databases completely in code and build a complete working mini db application!
Visual Basic Database Tutorial – Part 5
Database diva Karl Moore continues his epic series by taking a look at good db design, primary keys, relationships and more.
Visual Basic Database Tutorial – Part 6
Karl Moore continues his epic database tutorial by taking a sneak geek peek at good database design and validation, before moving on to create a simple VB-integrated report.
Visual Basic Database Tutorial – Part 7
VB-World database dude Karl Moore concludes his epic tutorial series by uncovering the mysteries of Oracle, SQL Server and the oh-so-groovy VB database tools.