08. Primary Foreign key Table relationships

Primary Foreign key Table relationships

XI. Creating Table Relationships

If you create a database to manage something, the database must contains at least two tables.  In addition, the two tables must have relationship to each other. To know what is relationship, how to create relationship and the type of relationship read following points.

XI.1. What Is Relationship?

A relationship of two tables occurs through a primary key of a table. This primary key becomes the foreign key of another table.

XI.2. What Is Primary Key

Primary Key is a key that you set on a field of the table to prohibit the duplicate value in the table. It also has the role to create the relationship between the tables. The relationship cannot happen if the main table does not have primary and the child table do not contain foreign key.

 

XI.3. What Is Foreign Key?

Foreign key is a primary key field of a table that exists to another table.

 

XI.4. Type of Relationship

There are three types of table relationship include one to one, one to many, and many to many relationship.

- One-to-one relationship:

 A relationship that each record in the first table can have only one matching to each record of the second table and vice versa. This relationship happens in case you define it. For example, an employee can have only one legal wife. From this example, you can create two tables:

- TblEmployee(EmpID, EmpName, Sex, DOB, Address, Phone, SpouseID)

- TblSpouse(SpouseID, SpouseName, Sex, DOB, Phone)

The two tables relate to each other through SpouseID fields. SpouseID in the TblEmployee is a foreign key referencing to the SpouseID primary key in the TblSpouse.

Note: EmpID and SpouseID represent primary keys of the tables.

You can match one-to-one relationship between the two table as the figure below:

 

Remark: To create one-to-one relationship, you must set indexed on the SpouseID foreign key by selecting Yes(No Duplicates).

- One-to-many relationship: 

In this relationship, each record in the first table can match to multiple records in the second table. For instance, a publisher can publish many books, but one book can be published by only one publisher. Through this example, you can create two tables:

- TblPublisher(PubID, PubName. Addr)

- TblBook(BookID, PubID, Title, ISBN)

The two tables relate to each other through PubID fields. PubID in the TblBook is a foreign key referencing to the PubID primary key in the TblPublisher.

You can match one-to-many relationship between the two table as the figure:

 

- Many to many relationship: 

A relationship that each record in the first table can match to many records in the second table and each record in the second can match to many records in the first table. For example, one student can study in a room or many rooms and one room can contain one student or many students.

 In this case, you can create two tables:

- TblStudent(StudentID, StudentName, Sex, DOB, Address, Phone)

- TblRoom(RoomID, RoomName, Floor)

In many-to-many relationship, only two tables are not valid to create a relationship. You need to create the third table to connect the two tables. The third table calls 

TblRoomStudent that contains the foreign keys of the two tables.

- TblRoomStudent(StudentID, RoomID)

You can match many-to-many relationship between the three table as the figure: