Understanding Relationship in Database (RDBMS)

There are three type of relationships

1) One to one

2) One to many

3) Many to many

Say we have table1 and table2

For one to one relationship, a record(row) in table1 will have at most one matching record or row in table2

I.e. it mustn’t have two matching records or no matching records in table2.

For one to many, a record in table1 can have more than one record in table2 but not vice versa

Let’s take an example,

Say we have a database which saves information about Guys and whom they are dating.

We have two tables in our database Guys and Girls

Here in above example Guy ID and Girl ID are primary keys of their respective table.

Say Andrew is dating Girl1, Bob – Girl2 and Craig is dating Girl3.

So we are having a one to one relationship over there.

So in this case we need to modify the Girls table to have a Guy id foreign key in it.

Now let say one guy has started dating more than one girl.

i.e. Andrew has started dating Girl1 and say a new Girl4

That takes us to one to many relationships from Guys to Girls table.

Now to accommodate this change we can modify our Girls table like this

Now say after few days, comes a time where girls have also started dating more than one boy i.e. many to many relationships

So the thing to do over here is to add another table which is called Junction Table, Associate Table or linking Table which will contain primary key columns of both girls and guys table.

Let see it with an example

Andrew is now dating Girl1 and Girl2 and

Now Girl3 has started dating Bob and Craig

so our junction table will look like this

It will contain primary key of both the Girls and Boys table.

Why do we need one to one relationship when we can easily accommodate the values in a single row?

For isolating some sensitive data. Employee table with its salary information stored in a separate table.

Putting large data in a separate table say a BLOB or Image type in which we are saving an image or document.

This type of relationship is typically used to improve performance.

And for our One to Many and Many to Many relationship, they help in maintenance.

If we had been storing multiple values in a column it would have been very difficult to query that table.

For e.g. say we have hobby column in our student table

And hobby has multiple values

What would be select query to find student name having hobby as reading or update swimming with dancing. It would be too difficult to write such a query.

So if we have these values separated in another table we can easily query it.

Bye