Adding Relationships to a Database MySQL using SQLyog Step 5

I am going to explain how to relate the database properly using the SQL Enterprise Manager (SQLyog), and then I will explain what it means.

Following the tutorial, we have tables called users, userbook, book and bookcategory. We need to establish relationships between them.  The users table will keep track of the users in our system, the book table keeps track of the books the user has read, and the bookcategory is the table that keeps track of what category the books are in. The userbook is the table that is the relationship between the user table and the book table.  This table is to establish the many to many relationship between users and the books they read.  A book can be read by many users and a user can read many books.

You want to relate users to the userbook table and you want to relate book to the userbook table. You will also want to relate book to the bookcategory table.

Select the userbook table with the right mouse button after you opened up the SQLyog. You will want to select relationships/foreign keys from the menu. You will want to press new to create a new relationship. A create relationship table will popup. The source is the table you selected which is the userbook table and the target column is the table that you are going to relate this table too. The ids are the columns that the database will usually be related to because they are unique. You first want to select the target table in the referenced table (primary key table) drop down box. In that drop down box you should select the user table. The current table will be the foreign key table.

The Source Column will be the UserId and the Target column will be Id. You should create another relationship in the userbook table. The primary key table will be the book table. The Source Column will be the BookId. You will need to change the constraint name to be FK_bookbook to be a different name then the FK_userbook since the other relationship already took that name. You can then press create to create the second relationship. You can now close the Manage Relationship GUI.

You will want to open up the Manage relationship table on the book table. Select new to create a relationship on this table. You want to select book from the referenced table. The Source Column should be BookCategoryId so the book table now has a relationship with the category table. The Target Column should be Id. Press create to create that relationship and close the manage relationships GUI.

You have now added relationships to these tables.

There are different relationships a database can have between tables.
Tables are related to each other by keys. A table usually will have a primary key which is the key to the table and possibly a foreign key which is the link to another table.

A one to one relationship between tables will mean that an employee can work in only one department. If it is a “one to one” relationship, then that will also mean that a department can have only one employee which isn’t usually the case; therefore, that would be a “one to many” relationship. In our database, we will have a “one to many” relationship between the book table and the bookcategory table. That means that a book can have only one category to classify it, but the category can have more then one book in that category. A “many to many” relationship in a database is a little more complicated to implement. In our case, we have a many to many relationship with the user and the book table. The user can read more then one book and the books can be read by more then one user. A solution to a many to many relationship is to compose the two tables into two relationships with a newly created table to intervene between the two. Our intervening table is the userbook table.

In my next post I will discuss querying the database. You will need to know how to insert data, update data, and delete data from the database.

Go To Step 6