In my previous tutorial I discuss on “How to Add Table to Your Existing Database”. Now, I will explain to you on how to create database relationships between one or more tables. Relationship is very important to prevent data inconsistency in your database. For example, you cannot add record in the related table without first adding a record in the primary table.
A relationship exists when two tables are related using a primary key and a foreign key.
When you add the tables in the previous tutorial it automatically creates necessary relationship in your table because I just copied it from the database that I have already made. But that relationship is not visible in the Database Diagram.
To create relationship between two or more tables all you need is to create a new database diagram. Please follow the instruction below.
1. Right click on the “Database Diagrams” and click "New Database Diagram”.
2. Add all the tables to the Database Diagram.
3. Now arrange the table in the database diagram.
4. As you can see on the Borrow and BorrowDetails table the relationship is already defined. This is because we created this table using the script on the previous tutorial. Now let us add a relationship to the Return and ReturnDetails table.
5. If you drag the ReturnID field correctly you will see a dialog box as you drop the field to another table similar to the following image.
6. Expand “Insert and Update Specification” and set the Delete Rule and Update Rule to Cascade. This will ensure that the ReturnDetails table cannot accept any data that don’t have a relation from the primary table which is Return.
As you click the OK button you will see that the Return and ReturnDetails table is already related to each other.
One to Many Relationship
Borrow/BorrowDetails and Return/ReturnDetails is an example of a one to many relationship. That’s why I give priority on these tables. If you have also heard about Parent/Child and Master/Detail form, just think of these tables. Borrow or Return table is a Parent or Master and BorrowDetails or ReturnDetails table is a Child or Detail form.
Now the remaining tables are not as important when it comes to defining a relationship. But still they are needed to be set in order to apply certain rule. Like for example in the case of Member table, you are not allowed to delete a member record if the record exists on the Borrow table.
In the actual scenario, for instance, John Smith is a member and he borrowed one or more books and it is stored in the Borrow table. In this case you should no delete “John Smith” from the member’s record to avoid data inconsistency. If you happen to delete it from the database, how can you determine who borrowed that books if the member doesn’t exist in the members table.
Now let us create relationship for the remaining table.
Members table is related to Course table. Drag CourseID from Members table to CourseID in the Course table just like what you have done in the Return and ReturnDetails table.
Review the following image if you have the same dialog in your computer screen.
Books table is also related to Subject table. Drag SubjectID from Books table to SubjectID in the Subject table.
Review the following image if you have the same dialog in your computer screen.
Here’s the complete relationship in our database diagram.
In the next tutorial I will explain to you on how to Attach and Detach a database using Management Studio Express.
Back to Visual Basic .NET 2008 Tutorial.
