Database Relations

There will be many cases when the data in one table is related to the data in another table. This connection between two tables is called a relation. When there is such a relation between two tables, these tables will be connected by inserting the primary key of one table into the corresponding row of the other table. The field used in such a way to connect the two tables is known as the foreign key.

There are two main kinds of relations - One to Many and Many to Many. There is also the One to One relation - but that's rarely used(mostly for optimization purposes) - so we don't have to worry about that.

One to Many

One to many relation is the relation where a row in one table has multiple corresponding rows in the other table. In our table, one 'Character will have multiple quotes - but each 'Quote' has just one Character. An image will make this clearer...

The 'Quote' table has 3 fields id, character_id, and quote. The character_id field is the foreign key. It will have the id of the character who spoke the quote - as shown here...

Character Table

Quote Table

The Christmas joke was made by the character with id 3 - that is Bart. And the 'wedding' quote has the character_id 1 that means it is spoken by Homer.

Implementation

So how do you implement a One to Many relation? Just remember this rule of thumb: The 'Many' table gets a new field that contains a reference to the 'One' table's primary key. In the above example, the 'Many' table is the 'Quote' table and the 'One' table is the Character table. So the Quote table gets a field with reference to the Character table - and that field is the character_id field.

Many to Many

This is where it gets complicated - the Many to Many relation is where each row in the first table can be related to multiple rows in the second table - and each row in the second table is related to multiple row is the first table. To understand this relation, take the example of the Episode and Character table relation. Each Character has multiple Episodes and each Episode has multiple Characters in it. This is a 'Many to Many' relation.

Implementation of a Many to Many Relation

The rule here is to create another table. It must have two fields - each referencing one table. Lets see how this is implemented in our database.

Character Table

Episode Table

Reference Table (CharacterEpisode)

In the CharacterEpisode table the character_id points to the primary key in the Character table and the episode_id field points to the ID in the 'Episode' table. So we have two forign keys in this case.

By the way, if there are some die hard Simpson fans in the audience, please forgive me. The CharacterEpisode table does not have the accurate data - I simply inserted some random numbers into it. I wanted to get the correct data - but my laziness prevented me from doing that.

Queries

Now try out some queries within the many to many relation structure. I hope you remember the stuff taught in the JOIN page....

Find all the episodes in which Homer(Character ID 1) appeared...

Find all the Characters that appeared in Episode 'Bart the Genius'

Now try some on your own. As always, the answers are available.

• Find the name of all episodes where Lisa appeared.
• Find the total number of episodes that had Bart in it.